Output a MySQL formatted string

Question

Suppose I have a query likes:

SELECT name FROM product WHERE id = 28;

The result is "HTC Desire HD"

I want this result to be replaced with XX in the string "I Like XX", after replacing, the string is "I Like HTC Desire HD"

Likes the PHP printf function, can I use MySQL to do that?

this is not ideal.use presentation language like php to do the formatting should be better — ajreal
– ajreal, Commented Sep 6, 2011 at 4:47
@ajreal: It seems about the same either way. Similar to querying someone's full name as SELECT CONCAT(firstName, ' ', lastName). Unless that's just bad practice I'm not aware of (I'm no expert on SQL). — Josh Darnell
– Josh Darnell, Commented Sep 6, 2011 at 5:28
is not the same.the formatted strings are meant for presentation let the presentation language to do the job — ajreal
– ajreal, Commented Sep 6, 2011 at 5:31
@ajreal PHP does not answer the question. You don't know the context. If you need a fully SQL report, you'll need pure SQL formatting. — Natacha
– Natacha, Commented Aug 9, 2023 at 19:01

evandrix · Accepted Answer · 2018-10-27 18:58:14Z

9

That would be:

select 'I Like ' || name from product where id = 28;

in regular SQL. I'm not entirely certain that will work in MySQL unless you have PIPES_AS_CONCAT configured, but the equivalent:

select CONCAT('I Like ', name) from product where id = 28;

should be fine.

evandrix

6,2464 gold badges30 silver badges38 bronze badges

answered Sep 6, 2011 at 4:34

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I think in MySQL it's double pipes (||), as long as your database is set to PIPES_AS_CONCAT mode :-) +1 !

Brad Christie · Accepted Answer · 2011-09-06 04:35:35Z

4

SELECT CONCAT('I Like ',name)
FROM   product
WHERE  id = 28;

answered Sep 6, 2011 at 4:35

Brad Christie

102k16 gold badges160 silver badges200 bronze badges