Need sql query to string split and normalize data


 
Thread Tools Search this Thread
Top Forums Programming Need sql query to string split and normalize data
# 1  
Old 05-11-2017
Need sql query to string split and normalize data

Hello gurus,
I have data in one of the oracle tables as as below:

Code:
Column 1    Column 2
1               NY,NJ,CA
2               US,UK,
3               AS,EU,NA

fyi, Column 2 above has data delimited with a comma as shown.

I need a sql query the produce the below output in two columns as.
Code:
Column 1        Column 2
1                   NY
1                   NJ
1                   CA
2                   US
2                   UK
3                   AS
3                   EU
3                   NA


Basically, I need to split data in one field based on a delimiter which is a comma and then normalize the data to get the required output. I have been trying sql using regex but without success. Any inputs are appreciated.
Thanks,
Carl

Moderator's Comments:
Mod Comment edit by bakunin: please use CODE-tags for data and file content too. Thank you.

Last edited by bakunin; 05-11-2017 at 10:05 AM..
# 2  
Old 05-11-2017
as a workaround for post-processing:
Code:
awk 'FNR==1{print;next}{n=split($2,a,",");for(i=1;i<=n;i++) if(a[i]) print $1,a[i]}' mySQLextactedFile

# 3  
Old 05-11-2017
Quote:
Originally Posted by vgersh99
as a workaround for post-processing:
Code:
awk 'FNR==1{print;next}{n=split($2,a,",");for(i=1;i<=n;i++) if(a[i]) print $1,a[i]}' mySQLextactedFile

Thanks but need the solution in sql query. Its easy to implement using sed/awk.
# 4  
Old 05-11-2017
Hi,
Example (in red for you) :
Code:
with T As
  (select 123 as c1, 'NY,NJ,CA' as c2 from dual
  union
  select 124 as c1, 'NY,PA,' as c2 from dual)
  SELECT DISTINCT C1, regexp_substr(C2,'[^,]+', 1, LEVEL)
  FROM T
  CONNECT BY regexp_substr(C2, '[^,]+', 1, LEVEL) IS NOT NULL
  ORDER BY C1;

Result:
Code:
        C1 REGEXP_SUBSTR(C2,'[^,]+'
---------- ------------------------
       123 CA
       123 NJ
       123 NY
       124 NY
       124 PA

Regards.
This User Gave Thanks to disedorgue For This Post:
# 5  
Old 05-11-2017
Quote:
Originally Posted by disedorgue
Hi,
Example (in red for you) :
Code:
with T As
  (select 123 as c1, 'NY,NJ,CA' as c2 from dual
  union
  select 124 as c1, 'NY,PA,' as c2 from dual)
  SELECT DISTINCT C1, regexp_substr(C2,'[^,]+', 1, LEVEL)
  FROM T
  CONNECT BY regexp_substr(C2, '[^,]+', 1, LEVEL) IS NOT NULL
  ORDER BY C1;

Result:
Code:
        C1 REGEXP_SUBSTR(C2,'[^,]+'
---------- ------------------------
       123 CA
       123 NJ
       123 NY
       124 NY
       124 PA

Regards.
works great although runs a little slow, thanks a lot.
# 6  
Old 05-12-2017
Code:
SQL>
SQL> --
SQL> select * from t;
  
         X Y
---------- ----------------------------------------
         1 NY,NJ,CA
         2 US,UK
         3 AS,EU,NA
         4 AAA,BBBB,C,DDDDD,EE,F,GGGGGG
         5
         6 XYZ
  
 6 rows selected.
  
SQL>
SQL> -- Using SUBSTR, INSTR functions.
SQL> select x,
  2         case when iter.pos = 1 and length(y)-length(replace(y,','))+1 = 1 then y
  3              when iter.pos = 1 then substr(y,1,instr(y,',',1,iter.pos)-1)
  4              when iter.pos = length(y)-length(replace(y,','))+1 then substr(y,instr(y,',',1,iter.pos-1)+1)
  5              else substr(y, instr(y,',',1,iter.pos-1)+1, instr(y,',',1,iter.pos) - instr(y,',',1,iter.pos-1) - 1)
  6         end as token
  7    from t,
  8         (  select level as pos
  9              from dual
 10           connect by level <= (select max(length(y)-length(replace(y,','))+1) from t)
 11         ) iter
 12   where iter.pos <= nvl(length(y)-length(replace(y,','))+1,1)
 13   order by x, pos
 14  ;
  
         X TOKEN
---------- ----------------------------------------
         1 NY
         1 NJ
         1 CA
         2 US
         2 UK
         3 AS
         3 EU
         3 NA
         4 AAA
         4 BBBB
         4 C
         4 DDDDD
         4 EE
         4 F
         4 GGGGGG
         5
         6 XYZ
  
 17 rows selected.
  
SQL>
SQL> -- Same query in stages.
SQL> with iter(pos) as (
  2      select level as pos
  3        from dual
  4     connect by level <= (select max(length(y) - length(replace(y,',')) + 1) from t)
  5  ),
  6  data(x, y, token_count) as (
  7      select x, y, length(y) - length(replace(y, ',')) + 1 as token_count
  8        from t
  9  ),
 10  combined as (
 11      select d.x, d.y, iter.pos, d.token_count,
 12             case when iter.pos > 1 then instr(y, ',', 1, iter.pos-1)
 13             end as prev_indx,
 14             instr(y, ',', 1, iter.pos) as indx
 15        from data d, iter
 16       where iter.pos <= nvl(d.token_count, 1)
 17  )
 18  select x,
 19         case when pos = 1 and token_count = 1 then y
 20              when pos = 1 then substr(y, 1, indx - 1)
 21              when pos = token_count then substr(y, prev_indx + 1)
 22              else substr(y, prev_indx + 1, indx - prev_indx - 1)
 23         end as token
 24    from combined
 25   order by x, pos
 26  ;
  
         X TOKEN
---------- ----------------------------------------
         1 NY
         1 NJ
         1 CA
         2 US
         2 UK
         3 AS
         3 EU
         3 NA
         4 AAA
         4 BBBB
         4 C
         4 DDDDD
         4 EE
         4 F
         4 GGGGGG
         5
         6 XYZ
  
 17 rows selected.
  
SQL>
SQL>
SQL> -- Another one using regular expressions.
SQL> -- regexp_count is in version 11g Release 1 and higher
SQL> select x,
  2         regexp_substr(y,'[^,]+',1,iter.pos) as token
  3    from t,
  4         (  select level as pos
  5              from dual
  6           connect by level <= (select max(regexp_count(y,',')+1) from t)
  7         ) iter
  8   where iter.pos <= nvl(regexp_count(y,',')+1,1)
  9   order by x, pos
 10  ;
  
         X TOKEN
---------- ----------------------------------------
         1 NY
         1 NJ
         1 CA
         2 US
         2 UK
         3 AS
         3 EU
         3 NA
         4 AAA
         4 BBBB
         4 C
         4 DDDDD
         4 EE
         4 F
         4 GGGGGG
         5
         6 XYZ
  
 17 rows selected.
  
SQL>
SQL>


Last edited by durden_tyler; 05-12-2017 at 12:52 PM..
This User Gave Thanks to durden_tyler For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk: split and gensub query

Hi All, Thanks for answering my previous question. Could you please explain the highlighted code? awk -v pos='9 27 39 54 59 64 71 78 83 103 108' 'BEGIN{split(pos,var)} {for (i in var) $0=gensub(/./,"|",var)} 1' test.txt | head I understood that the split function splits the pos string into... (2 Replies)
Discussion started by: mrcool4
2 Replies

2. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

3. Web Development

Iplanet webserver retaining the URI query string data.

Current Situation: 1. Visit: https://xyz.com/2015/september?trackingparam=1234 2. The URL is missing the trailing / after the “september” directory 3. The URL is redirected and rewritten to: https://xyz.com/2015/september/ , dropping the query string data. Note:... (0 Replies)
Discussion started by: raghur77
0 Replies

4. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

5. Shell Programming and Scripting

How to pass string into sql query?

Hi Gurus, I have a request which needs to pass string into sql. dummy code as below: sqlplus -s user/password@instance << EOF >>output.txt set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep , select emp_no, emp_name from emp where emp_no in ('a', 'b', 'c'); exit;... (4 Replies)
Discussion started by: ken6503
4 Replies

6. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

7. UNIX for Dummies Questions & Answers

Normalize Data and write to a flat file

All, Can anyone please help me with the below scenario. I have a Flat file of the below format. ID|Name|Level|Type|Zip|MAD|Risk|Band|Salesl|Dealer|CID|AType|CValue|LV|HV|DCode|TR|DU|NStartDate|UserRole|WFlag|EOption|PName|NActivationDate|Os|Orig|Cus|OType|ORequired|DType 03|... (10 Replies)
Discussion started by: sp999
10 Replies

8. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

9. Shell Programming and Scripting

how to use data in unix text file as input to an sql query from shell

Hi, I have data in my text file something like this. adams robert ahmed gibbs I want to use this data line by line as input to an sql query which i run by connecting to an oracle database from shell. If you have code for similar scenario , please ehlp. I want the output of the sql query... (7 Replies)
Discussion started by: rdhanek
7 Replies

10. UNIX for Dummies Questions & Answers

How do I use SQL to query based off file data?

This is basically what I want to do: I have a file that contains single lines of IDs. I want to query the oracle database using these IDs to get a count of which ones match a certain condition. the basic idea is: cat myfile | while read id do $id in select count(PC.ptcpnt_id) from... (4 Replies)
Discussion started by: whoknows
4 Replies
Login or Register to Ask a Question