Put value between 2 single quote Oracle Sql

Question

I have stringA='100,1000,11,9200';

I want the out to be like this stringA='100','1000','11','9200';

score 0 · Accepted Answer · answered Mar 15 '21 at 08:38

Replace along with some concatenation does it:

SQL> with test (a) as
  2    (select '100,1000,11,9200' from dual)
  3  select chr(39) || replace(a, ',', chr(39) ||','||chr(39)) || chr(39) new_a
  4  from test;

NEW_A
------------------------
'100','1000','11','9200'

SQL>

If you create that string (maybe using listagg?), then do it at once:

SQL> with test (a) as
  2    (select 100  from dual union all
  3     select 1000 from dual union all
  4     select 11   from dual union all
  5     select 9200 from dual
  6    )
  7  select chr(39) || listagg(a, chr(39) ||','|| chr(39)) within group (order by null) || chr(39) new_a
  8  from test;

NEW_A
--------------------------------------------------------------------------------
'100','1000','11','9200'

SQL>

dear, is this solution will work if stringA values increase or decrease ?? — ghalib ali, Mar 15 '21 at 08:48

score 0 · Answer 2 · edited Mar 15 '21 at 08:46

0

I think this is a possible solution. Replace all commas with ',' and concatenate the superscript in front of and after the string. In sql server it works, it should work for oracle as well.

SELECT ''''+REPLACE('100,1000,11,9200',',',''',''')+''''

edited Mar 15 '21 at 08:46

Serg

22,285
5
21
48

answered Mar 15 '21 at 08:40

FauconThePonthie

56
3

Put value between 2 single quote Oracle Sql

2 Answers2