String modification for table column values

Question

I need a solution for string from column values like 'abc xyz-1234' to 'xyz, abc'. What will be the Oracle SQL query for above solution.

SELECT   SUBSTR (COLUMN_NAME,
                 INSTR (' ', COLUMN_NAME) + 1,
                 LENGTH (5) - SUBSTR ('%[^a-z,A-Z, ]%', COLUMN_NAME) - 1)
       + ','
       + SUBSTR (COLUMN_NAME, 1, INSTR (' ', COLUMN_NAME) - 1)
  FROM table_name;

I'm getting Invalid number error in Oracle SQL.

Answer 1

You can use:

SELECT SUBSTR(column_name, spos + 1, hpos - spos - 1)
       || ','
       || SUBSTR(column_name, 1, spos - 1) AS formatted_column_name
FROM   (
  SELECT column_name,
         INSTR(column_name, ' ') AS spos,
         INSTR(column_name, '-') AS hpos
  FROM   table_name
);

or, using regular expressions (which is less to type but likely to be slower):

SELECT REGEXP_REPLACE(column_name, '^(.*?) (.*?)-.*$', '\2,\1')
         AS formatted_column_name
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (column_name) AS
  SELECT 'abc xyz-1234' FROM DUAL;

Both output:

FORMATTED_COLUMN_NAME
xyz,abc

fiddle

Answer 2

Alternatively, check whether regular expressions suit your needs.

Sample data:

SQL> with test (col) as
  2    (select 'abc xyz-1234' from dual)

Query:

  3  select regexp_substr(col, '\w+', 1, 2) ||', '||
  4         regexp_substr(col, '\w+', 1, 1) as result
  5  from test;

RESULT
--------
xyz, abc

SQL>