Sponsored Content
Top Forums Programming Need sql query to string split and normalize data Post 302997327 by calredd on Thursday 11th of May 2017 12:25:23 PM
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.
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
CheckDigits::M11_010(3pm)				User Contributed Perl Documentation				 CheckDigits::M11_010(3pm)

NAME
CheckDigits::M11_010 - compute check digits AHV number (CH) SYNOPSIS
use Algorithm::CheckDigits; $ahv = CheckDigits('ahv_ch'); if ($ahv->is_valid('123.45.678.113')) { # do something } $cn = $ahv->complete('123.45.678.11'); # $cn = '123.45.678.113' $cd = $ahv->checkdigit('123.45.678.113'); # $cd = '3' $bn = $ahv->basenumber('123.45.678.113'); # $bn = '123.45.678.11'; DESCRIPTION
ALGORITHM 1. Beginning left every digit is weighted with 5,4,3,2,7,6,5,4,3,2. 2. The weighted digits are added. 3. The sum from step 2 is taken modulo 11. 4. The checkdigit is 11 minus the sum from step 3. Is the difference 10, the number won't be taken. If the difference is 11, the checkdigit is 0. METHODS is_valid($number) Returns true only if $number consists solely of numbers and hyphens and the two digits in the middle are valid check digits according to the algorithm given above. Returns false otherwise, complete($number) The check digit for $number is computed and inserted into the middle of $number. Returns the complete number with check digit or '' if $number does not consist solely of digits, hyphens and spaces. basenumber($number) Returns the basenumber of $number if $number has a valid check digit. Return '' otherwise. checkdigit($number) Returns the check digits of $number if $number has valid check digits. Return '' otherwise. EXPORT None by default. AUTHOR
Mathias Weidner, <mathias@weidner.in-bad-schmiedeberg.de> SEE ALSO
perl, CheckDigits, www.pruefziffernberechnung.de, perl v5.10.0 2008-05-17 CheckDigits::M11_010(3pm)
All times are GMT -4. The time now is 07:41 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy