How can I get the value of a variable inside a string in SQL?

Question

Let a table 'Example' has columns id, value.

| id | value |
|--- | ----- |
|1   | randm |

Now I want to write something like this

SELECT id, 'x is 'value'' as result
FROM Example
WHERE id = 1;

which prints

| id | result |
| -- | ------ |
| 1  | x is 'randm' |

How can I do this? In python, if we use {} in the middle of a string, we could pass variables, which is similar to what I want in SQL.

Have you tried to use string concatenation for this, either through `||` or through `CONCAT`? — Nico Haase, Jun 18 '21 at 13:51
Pick your poison: https://www.postgresql.org/docs/9.1/functions-string.html — sp00m, Jun 18 '21 at 13:51
@NicoHaase I don't just want to concatenate. I want the value in single quotes inside a string. — Gowtham Vurimi, Jun 18 '21 at 14:13

JNevill · Answer 1 · 2021-06-18T14:16:04.977

1

The operator || will concatenate strings in Postgres, so it's just:

 SELECT id, 'x is ''' || value || '''' AS result
 FROM Example
 WHERE id = 1;

edited Jun 18 '21 at 14:16

answered Jun 18 '21 at 13:51

JNevill

46,980
4
38
63

This is giving x is randm, but I want x is 'randm'. – Gowtham Vurimi Jun 18 '21 at 14:14
@GowthamVurimi I've updated. I'm including an escaped single quote (double single quote) in the string literals. – JNevill Jun 18 '21 at 14:16

Abelisto · Accepted Answer · 2021-06-18T14:02:24.577

SELECT id, format('x is %L', value) as result
FROM Example
WHERE id = 1;

SELECT id, format('x is %s', quote_literal(value)) as result
FROM Example
WHERE id = 1;

SELECT id, format('x is ''%s''', value) as result
FROM Example
WHERE id = 1;

SELECT id, format($$x is '%s'$$, value) as result
FROM Example
WHERE id = 1;

https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

How can I get the value of a variable inside a string in SQL?

2 Answers2