Separate the values from the data column

Question

I have a column with data like

I~A~G~S

How could i separate them using select query.

Output : Updated

  Rows       Column(Data)
  First      I
  Second     A
  Third      G
  Fourth     S

is this what you are expecting? select replace('I~A~G~S','~',' ') as x from dual; you can use replace function in oracle. you replace the ~ with with space. — Shann, Nov 27 '13 at 05:45
possible duplicate http://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows — Noel, Nov 27 '13 at 06:01

vhadalgi · Answer 1 · 2013-11-27T06:12:53.577

2

declare @s varchar(10)
set @s='I~A~G~S'

select replace(@s,'~',' ')

for updated question

create table #vij11 (s varchar(100))

insert into #vij11(s) values ('I~A~G~S')
SELECT   
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [s],  
         CAST ('<M>' + REPLACE([s], '~', '</M><M>') + '</M>' AS XML) AS String  
     FROM  #vij11) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

fiddle demo

edited Nov 27 '13 at 06:12

answered Nov 27 '13 at 05:46

vhadalgi

7,027
6
38
67

Could i get the same as values in different-2 row. like I in first row then A in 2nd row. – Java_Alert Nov 27 '13 at 05:50
what you mean ? like I A G S *2 – vhadalgi Nov 27 '13 at 05:52
FIRST ROW I, SECOND ROW A , THIRD ROW G , FOURTH ROW S – Java_Alert Nov 27 '13 at 05:53
@Java_Alert i have updated the answer above for splitting the string in different rows. please check – Shann Nov 27 '13 at 06:09
can we get I~A~G~S into different- different variables like income = I, Age=A, Gender=G and State=S; – Java_Alert Nov 29 '13 at 06:46

Shann · Accepted Answer · 2013-11-27T06:09:04.910

2

if you are using oracle database then it should be as follows:

select replace('I~A~G~S','~',' ') as x from dual;

select replace(<fieldname>,'~',' ') from <tablename>

as for the question of splitting the string in different rows please check the below query.

SELECT trim(regexp_substr(replace('I~A~G~S','~',','), '[^,]+', 1, LEVEL)) str_2_tab
  FROM dual
  CONNECT BY LEVEL <= regexp_count(replace('I~A~G~S','~',','), ',')+1;

edited Nov 27 '13 at 06:09

answered Nov 27 '13 at 05:52

Shann

660
1
6
19

you are welcome. if the answer has answered all you questions, please mark as answered. thnxs – Shann Nov 27 '13 at 06:33
can we get I~A~G~S into different- different variables like income = I, Age=A, Gender=G and State=S; – Java_Alert Nov 29 '13 at 06:45

Separate the values from the data column

2 Answers2