2

I am trying to write a select query which should return the column value wrapped in single quote. Say the column (ABC) has

Values: 123
        567

The query should return the 

Output: '123'
        '567'
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
DUnkn0wn1
  • 401
  • 1
  • 8
  • 23
  • Select '\''||ABC||'\'' from table and Select '''ABC''' from table 1st query throws an exception nonstandard use of \' in a string literal 2nd query returns the column name in single quotes rather that the data 'ABC' – DUnkn0wn1 Aug 05 '14 at 15:59

2 Answers2

5

While dealing with numerical data, you can simply concatenate. NULL values stay NULL. But for character data (or similar) that might need escaping, use proper functions.

quote_nullable() or quote_literal() - depending on whether you have NULL values:

SELECT quote_nullable(val) AS quoted_val FROM tbl;

Details for quoting:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

I tend to escape the quote with another one, as in the standard SQL escape syntax:

nunks=# select '''I''m escaping a string''';
        ?column?         
-------------------------
 'I'm escaping a string'
(1 row)

When wrapping some output values, you'll have to concatenate with ||: 

nunks=# create table numbers (number int);
CREATE TABLE

nunks=# insert into numbers values (151515);
INSERT 0 1

nunks=# select number from numbers;
 number 
--------
 151515
(1 row)

nunks=# select ''''||number||'''' from numbers;
 ?column? 
----------
 '151515'
(1 row)

Maybe you'll find it clearer using the E syntax:

nunks=# select E'\''||number||E'\'' from numbers;
 ?column? 
----------
 '151515'
(1 row)
nunks
  • 436
  • 7
  • 10