Use string from select to select a specific column in mysql

Question

I'm trying to select a column based on a string I get from another select:

SELECT id, (
SELECT COLUMN_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' 
AND TABLE_NAME = 'a' 
AND DATA_TYPE = 'varchar'
ORDER BY `COLUMNS`.`ORDINAL_POSITION` ASC LIMIT 1) AS `name` 
FROM `a`

The problem is like this I'm getting only the string from the sub select not the column content of the main query.

(what I want to do in this case, is to return the value of the first column that is varchar)

Paul Campbell · Accepted Answer · 2018-03-13 10:36:39Z

You can do this with a dynamic query constructed in a stored procedure which you then call with the appropriate parameters.

I'd put the example in a sqlfiddle if I could find a way of querying the information_schema but you can just copy/paste this into your normal client and it should work.

I've gone a little further than your example by allowing you to select the id and first varchar column from any table but you can easily adjust the procedure and hardcode the database and table names if that's all you require.

CREATE DATABASE IF NOT EXISTS `mydb`;
USE `mydb`;

CREATE TABLE IF NOT EXISTS `mydb`.`tableA` (
 id INT auto_increment primary key,
 char_col char(3),
 varchar_col varchar(25) 
);

-- clear out any existing records
TRUNCATE `mydb`.`tableA`;

INSERT INTO `mydb`.`tableA` VALUES (null,'abc','varchar value abc');
INSERT INTO `mydb`.`tableA` VALUES (null,'def','varchar value def');
INSERT INTO `mydb`.`tableA` VALUES (null,'ghi','varchar value ghi');
INSERT INTO `mydb`.`tableA` VALUES (null,'klm','varchar value klm');

DELIMITER //

DROP PROCEDURE IF EXISTS `mydb`.`dyntest` //
CREATE PROCEDURE `mydb`.`dyntest` (IN dbname VARCHAR(64), IN tname VARCHAR(64))
BEGIN
  DECLARE colname VARCHAR(64);

  -- get the column name (as your example)
  SELECT `COLUMN_NAME` INTO colname 
  FROM `information_schema`.`COLUMNS` 
  WHERE `TABLE_SCHEMA` = dbname 
  AND `TABLE_NAME` = tname  
  AND `DATA_TYPE` = 'VARCHAR'
  ORDER BY `COLUMNS`.`ORDINAL_POSITION` ASC LIMIT 1; 

  -- construct the query 
  SET @sqlquery = CONCAT('SELECT `id`,`', colname , '` FROM `' , dbname, '`.`', tname, '`');

  -- Prepare and execute
  PREPARE stmt FROM @sqlquery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

This returns

mysql>  CALL `mydb`.`dyntest`('mydb','tableA'); 
+----+-------------------+
| id | varchar_col       |
+----+-------------------+
|  1 | varchar value abc |
|  2 | varchar value def |
|  3 | varchar value ghi |
|  4 | varchar value klm |
+----+-------------------+
4 rows in set (0.06 sec)

Collectives™ on Stack Overflow

Use string from select to select a specific column in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related