Parse with SQL

Applications Programming
1

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

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

Desired output

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

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

I am not sure I got your requirements, I will just show few samples of string functions (SQL Server)

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

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

2 Likes
3

Thank you.... works great :slight_smile: