CODEWITHSUNDEEP
sqloracle-databaseoracle11g
simplify_life
simplify_life

Reputation: 405

Insert character between string Oracle SQL

I need to insert character string after each character in Oracle SQL.

Example:

ABC  will A,B,C

DEFG will be D,E,F,G

This question gives only one character in string

Oracle insert character into a string

Upvotes: 2

Views: 5902

Answers (4)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626738

Since there is no way to negate the end of string in an Oracle regex (that does not support lookarounds), you may use

SELECT REGEXP_REPLACE(
    REGEXP_REPLACE('ABC', '([^,])([^,])','\1,\2'),
    '([^,])([^,])',
    '\1,\2')
    AS Result from dual

See the DB Fiddle. The point here is to use REGEXP_REPLACE with ([^,])([^,]) pattern twice to cater for consecutive matches.

The ([^,])([^,]) pattern matches any non-comma char into Group 1 (\1) and then any non-comma char into Group 2 (\2), and inserts a comma in between them.

Upvotes: 0

barbarity
barbarity

Reputation: 2488

Edit: As some fellows have mentioned, Oracle does not admit this regex. So my approach would be to do a regex to match all characters, add them a comma after the character and then removing the last comma.

WITH regex AS (SELECT REGEXP_REPLACE('ABC', '(.)', '\1,') as reg FROM dual) SELECT SUBSTR(reg, 1, length(reg)-1) FROM regex;

Note that with the solution of rtrim there could be errors if the string you want to parse has a final ending comma and you don't want to remove it.

Previous solution: (Not working on Oracle)

Check if this does the trick:

SELECT REGEXP_REPLACE('ABC', '(.)(?!$)', '\1,') FROM dual;

It does a regexp_replace of every character, but the last one for the same character followed by a ,

To see how regexp_replace works I recommend you: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

Upvotes: 5

Jaseer
Jaseer

Reputation: 52

SELECT rtrim(REGEXP_REPLACE('ABC', '(.)', '\1,'),',') "REGEXP_REPLACE" FROM dual;

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could do it using:

  • REGEXP_REPLACE
  • RTRIM

For example,

SQL> WITH sample_data AS(
  2  SELECT 'ABC' str FROM dual UNION ALL
  3  SELECT 'DEFG' str FROM dual UNION ALL
  4  SELECT 'XYZ' str FROM dual
  5  )
  6  -- end of sample_data mimicking a real table
  7  SELECT str,
  8    rtrim(regexp_replace(str, '(\w?)', '\1,'),',') new_str
  9  FROM sample_data;

STR  NEW_STR
---- ----------
ABC  A,B,C
DEFG D,E,F,G
XYZ  X,Y,Z

Upvotes: 2

Related Questions