MySQL – How to Append Value of Column with Double Quotes

concatMySQL

I have a table with a column that is TEXT type. In the column are numeric characters. What I'm trying to achieve is to wrap those characters in double quotes.

EXAMPLE:

NAME     ID     QTY
Apples   A1     1
Oranges  O1     1
Foo      F1     0

IDEAL OUTPUT:

NAME     ID     QTY
Apples   A1     "1"
Oranges  O1     "1"
Foo      F1     "0"

I attempted to run the following SELECT statement but it didn't give me the result I was expecting. Perhaps you can guide me in the right direction?

SELECT `qty`, CHAR('"'|| qty ||'"')
FROM `myTable`;

Thank You

Best Answer

Use the CONCAT function

SELECT NAME,ID,CONCAT('"',QTY,'"') QTY FROM `myTable`;

If you want single quotes, use the QUOTE function

SELECT NAME,ID,QUOTE(QTY) QTY FROM `myTable`;

Give it a Try !!!

Related Solutions

MySQL JSON – How to Remove String Quotes in MySQL 5.7 for JSON_EXTRACT

If you have MySQL 5.7.13 or later, you may use JSON_UNQUOTE() instead of JSON_EXTRACT() or ->> instead of ->. Example:

SELECT field->>"$.foo.barr" FROM table;

Mysql – ny way to concatenate a value in a column with different strings

I have the generic expression you need:

SELECT CONCAT(@num,IF(MOD(@num,100) IN (11,12,13),'th',
IF(MOD(@num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(@num,10)-1,2),'th')));

What this does is the following

If the last digit of a number is 1 (other than last two digits being 11), st is appended
If the last digit of a number is 2 (other than last two digits being 12), nd is appended
If the last digit of a number is 3 (other than last two digits being 13), rd is appended
All other two-digit combinations have th appended

To test this out, run the following

SELECT CONCAT(num,IF(MOD(num,100) IN (11,12,13),'th',
IF(MOD(num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(num,10)-1,2),'th'))) n FROM
(SELECT th*1000+h*100+t*10+u num FROM
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TH,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U) A
ORDER BY num;

UPDATE 2017-04-13 14:29 EDT

You asked me

Hey Rolando, I understand roughly how you have implemented this. But How would I work this around my code as so; select id, duration, @position := @position + 1 as position from (select @position := 0) x, (select id, duration from course order by duration asc) y;

SELECT id,CONCAT(position,IF(MOD(position,100) IN (11,12,13),'th',
IF(MOD(position,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(position,10)-1,2),'th')))
FROM 
(
    select id, duration, @position := @position + 1 as position
    from (select @position := 0) x,
    (select id, duration from course order by duration asc) y
) a;

Basically, I placed your query inside a subquery and applied the generic expression.

Best Answer

Related Solutions

MySQL JSON – How to Remove String Quotes in MySQL 5.7 for JSON_EXTRACT

Mysql – ny way to concatenate a value in a column with different strings

UPDATE 2017-04-13 14:29 EDT

Related Question