Login or Register to Ask a Question and Join Our Community


Programming

Parse with SQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Parse with SQL
# 1  
Old 05-28-2015
Parse with SQL
I am trying to parse a string using SQL but am too new and still learning. I have text in a control or field 685 that is variable, but always the same format.

field 685 input
Code:
arr[hg19] 2q33.3q34(200,900,700-209,000,000)x2 xxx

Desired output
Code:
 2:200900700-209000000

Basically, the # after the [hg19] but before the q (could also be a p) and the #'s in the () without the commas.

My attempt (though I'm not confident in it at all)

Thank you very much Smilie.

Code:
 SELECT PARSENAME(REPLACE('[685]', ' ', '.'), 2, 3, 4)

# 2  
Old 05-31-2015
I am not sure I got your requirements, I will just show few samples of string functions (SQL Server)
Code:

declare @str as varchar(128) = 'arr[hg19] 2q33.3q34(200,900,700-209,000,000)x2 xxx'
select
PATINDEX('%[qp]%', @str)-1 [pos1]
, substring(@str, PATINDEX('%[qp]%', @str)-1, 1) [pos2]
, ':' [:]
, CHARINDEX('(', @str) [pos_of_(]
, CHARINDEX(')', @str) [pos_of_)]
, SUBSTRING(@str, 
 CHARINDEX('(', @str)+1, 
 CHARINDEX(')', @str) - CHARINDEX('(', @str) - 1) [between ( and )]
, REPLACE(
SUBSTRING(@str, 
 CHARINDEX('(', @str)+1, 
 CHARINDEX(')', @str) - CHARINDEX('(', @str) - 1) , 
 ',', '')[commas removed]

results
Code:
pos1 pos2 : pos_of_( pos_of_) between ( and ) commas removed
11 2 : 20 44 200,900,700-209,000,000 200900700-209000000

hope these examples will help
These 2 Users Gave Thanks to migurus For This Post:
cmccabe jim mcnamara
# 3  
Old 06-05-2015
Thank you.... works great Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

3. Shell Programming and Scripting

awk script to parse SQL from Pro*C program

Hello, I have a Pro*C program which contains SQL sentences, such as: .... Pro*C sentences .... /*SQL 1*/ EXEC SQL SELECT t1.field1, t1.field2 INTO :w_field FROM TABLE1 t1, TABLE1 t2 WHERE t1.field1 = t2.field1 AND t1.ID = :wl_id; .... Pro*C sentences .... /*SQL 1*/ EXEC... (11 Replies)
Discussion started by: mvalonso
11 Replies

4. Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

Hi Forum. Need your expertise on the following question. I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited): Old File: SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */ ... (5 Replies)
Discussion started by: pchang
5 Replies

5. Shell Programming and Scripting

script to parse text file into sql commands

Hello all, I tried searching for something similiar before posting but couldn't come up with anything that fit what I need. In Linux, I'm trying to parse through a number of files and take the info in them and put it into mysql. My file is a dump from an expect script: ---filename... (3 Replies)
Discussion started by: hamanjam
3 Replies

6. Shell Programming and Scripting

sed parse a lign into a new sql file

Hi everyone, another question while using sed. my sed statement should parse every line in a file and store all "i" variable item a a new file. any wrong arguments here? Thanks a million. task_name => name, object_type => 'TABLE', attr1 => 'TestR3', attr2 => '$i', for i... (4 Replies)
Discussion started by: sundaygeek
4 Replies

7. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

8. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

9. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

10. Shell Programming and Scripting

"parse" SQL in bash or ksh

Hi! I've a text file programme1.out : CONNECT TO ORACLE (&CONXORA); CREATE TABLE TABLE1 AS SELECT ID_PERS FROM CONNECTION TO ORACLE ( SELECT DISTINCT PERS.ID_PERS FROM TAB_GRP GRP , TAB_PERS PERS WHERE DATE_DEB_GRP <=... (0 Replies)
Discussion started by: stagebi
0 Replies
Login or Register to Ask a Question