If I have something like this in SQL statement ('A','B','C'), how do I convert it into a column with multiple rows like this
col
---
A
B
C
I cannot change the way that string is created (as it is injected into SQL query from external program). For example, I cannot make it as ['A','B','C'] (replace with square brackets). I could wrap anything around it though like [('A','B','C')] or whatever.
Any help?
UPDATE 1
I have PostgreSQL 8.4.20
You could create an ARRAY from VALUES and then unnest it:
SELECT
unnest(ARRAY[col_a, col_b, col_c])
FROM
(VALUES('A','B','C')) AS x(col_a, col_b, col_c)
Result:
| unnest |
|--------|
| A |
| B |
| C |
Edit: you could also tweak jspcal's answer by using dollar quotes ($$) like this so you can concatenate your string into the SQL statement:
SELECT * FROM regexp_split_to_table(
regexp_replace(
$$('A','B','C','D','foo')$$,
'^\(''|''\)+', '', 'g'),
''','''
);
('A','B','C','D','E') without knowing ahead of time?
$$ to quote your string.('A though. There is extra ('... Still monkeying around here.
select unnest(cast(translate($$( 'A','B','C','D','foo' )$$, '()''', '{}"') as text[])); dbfiddle.uk/…The built-in regexp_split_to_table function will do this for you. Since you plan to inject it directly without escaping, use $$ (dollar quoting) from thibautg's answer.
select * from regexp_split_to_table(
regexp_replace($$('A','B','C')$$, '^\(''|''\)+', '', 'g'),
''','''
);
select regexp_split_to_table('A B C', E'\\s+');, it will work. But my original string is ('A','B','C'). So how to make it into 'A B C'?
regexp_replace.(''A'',''B'',''C'') but I can make it surrounded with quotes for example "('A','B','C')"
('A','B','C')string I have to deal with.