Sponsored Content
Top Forums Programming Need sql query to string split and normalize data Post 302997321 by disedorgue on Thursday 11th of May 2017 09:35:44 AM
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:
 

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
MSGFMT_CREATE(3)							 1							  MSGFMT_CREATE(3)

MessageFormatter::create - Constructs a new Message Formatter

	Object oriented style (method)

SYNOPSIS
publicstatic MessageFormatter MessageFormatter::create (string $locale, string $pattern) DESCRIPTION
Object oriented style (constructor): MessageFormatter::__construct (string $locale, string $pattern) Procedural style MessageFormatter msgfmt_create (string $locale, string $pattern) Constructs a new Message Formatter PARAMETERS
o $locale - The locale to use when formatting arguments o $pattern - The pattern string to stick arguments into. The pattern uses an 'apostrophe-friendly' syntax; it is run through umsg_autoQuoteA- postrophe before being interpreted. RETURN VALUES
The formatter object EXAMPLES
Example #1 msgfmt_create(3) example <?php $fmt = msgfmt_create("en_US", "{0,number,integer} monkeys on {1,number,integer} trees make {2,number} monkeys per tree"); echo msgfmt_format($fmt, array(4560, 123, 4560/123)); $fmt = msgfmt_create("de", "{0,number,integer} Affen auf {1,number,integer} Baumen sind {2,number} Affen pro Baum"); echo msgfmt_format($fmt, array(4560, 123, 4560/123)); ?> Example #2 OO example <?php $fmt = new MessageFormatter("en_US", "{0,number,integer} monkeys on {1,number,integer} trees make {2,number} monkeys per tree"); echo $fmt->format(array(4560, 123, 4560/123)); $fmt = new MessageFormatter("de", "{0,number,integer} Affen auf {1,number,integer} Baumen sind {2,number} Affen pro Baum"); echo $fmt->format(array(4560, 123, 4560/123)); ?> The above example will output: 4,560 monkeys on 123 trees make 37.073 monkeys per tree 4.560 Affen auf 123 Baumen sind 37,073 Affen pro Baum SEE ALSO
msgfmt_format(3), msgfmt_parse(3), msgfmt_get_error_code(3), msgfmt_get_error_message(3). PHP Documentation Group MSGFMT_CREATE(3)
All times are GMT -4. The time now is 07:26 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy