CODEWITHSUNDEEP
sqloracle-database
John
John

Reputation: 437

Oracle SQL - Separate Column Into Multiple Columns

I'm working with Oracle SQL and have a problem I don't know how to solve. I have a column in my table that consists of one letter (from a-f) and a number, then another letter (a-f) and another number etc.

Example of column:

a 30, d 800, b 233

Is there a way where I can separate the column into multiple columns in a select statement? So that I get ex: 

TEXTA  NUMBERA  TEXTB  NUMBERB   TEXTD   NUMBERD
  a     30       b      233       d       800

Upvotes: 1

Views: 84

Answers (2)

Ranjith
Ranjith

Reputation: 153

Try this code with the sample data,

WITH TEST_DATA AS
(SELECT 'a 30' COLUMN_A,'d 800' COLUMN_B,'b 233' COLUMN_C FROM DUAL)

SELECT SUBSTR(t.COLUMN_A, 1, INSTR(t.COLUMN_A, ' ')-1) AS TEXTA,
   SUBSTR(t.COLUMN_A, INSTR(t.COLUMN_A, ' ')+1) AS NUMBERA,
   SUBSTR(t.COLUMN_B, 1, INSTR(t.COLUMN_B, ' ')-1) AS TEXTD,
   SUBSTR(t.COLUMN_B, INSTR(t.COLUMN_B, ' ')+1) AS NUMBERD,
   SUBSTR(t.COLUMN_C, 1, INSTR(t.COLUMN_C, ' ')-1) AS TEXTB,
   SUBSTR(t.COLUMN_C, INSTR(t.COLUMN_C, ' ')+1) AS NUMBERB
FROM test_data T;

For your reference http://www.sqlfiddle.com/#!4/e6cff/1

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Are you sure you want them as columns like that?

If you do:

SELECT  REGEXP_SUBSTR (str, '[^, \s]+', 1, 1)    AS part_1
,       REGEXP_SUBSTR (str, '[^, \s]+', 1, 2)    AS part_2
,       REGEXP_SUBSTR (str, '[^, \s]+', 1, 3)    AS part_3
,       REGEXP_SUBSTR (str, '[^, \s]+', 1, 4)    AS part_4
, ...
FROM    table_x

str is your column name, table_x is your table

Upvotes: 2

Related Questions