string manipulation or cte

Question

string manipulation or cte

timscronin

SSCertifiable

Points: 6851
More actions
October 21, 2011 at 7:31 am

#250580

I have a column value like this
'Merged Facility Id:0286- Patient Id:999000 PHPatId:28488 with Facility Id:0286-C Patient Id:124013 PHPatId:678839'
What I need to strip out is
0286- 999000 28488 0286-c 124013 678839
and to shove these 6 values into columns in another tables

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

timscronin (10/21/2011)
I have a column value like this
'Merged Facility Id:0286- Patient Id:999000 PHPatId:28488 with Facility Id:0286-C Patient Id:124013 PHPatId:678839'
What I need to strip out is
0286- 999000 28488 0286-c 124013 678839
and to shove these 6 values into columns in another tables

Are the start/end positions of the columns fixed?

Or need to look for x characters after each colon?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

timscronin SSCertifiable Points: 6851 More actions · Answer 2

Not fixed, the patient id chan be from 3-12 characters long

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

timscronin (10/21/2011)
Not fixed, the patient id chan be from 3-12 characters long

Extracting "words" separated by delimiters - even heterogenous ones - from strings is TSQL bread-and-butter. The hard part is defining the rules for the delimiters. Can you be more specific, Tim? It's all rather vague at the moment. A few rows of sample data would help loads.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 4

each column will have data like below

'Merged Facility Id:0286- Patient Id:999000 PHPatId:28488 with Facility Id:0286-C Patient Id:124013 PHPatId:678839'

'Merged Facility Id:0232-C Patient Id:849000 PHPatId:28409 with Facility Id:0282-C Patient Id:189893 PHPatId:478839'

I needed the data separated so I can put it into columns in another table

So I can insert into tablea(mfacid,patid,phpatid,wfacid,newpatid,newphpatid)

values ('0286-',999000,28488,'0286-C',124013,678839)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SELECT

String,

[from Facility Id] = SUBSTRING(d.String,item1.Start,CHARINDEX('Patient Id:',String,0)-item1.Start),

[from Patient Id] = SUBSTRING(d.String,item2.Start,CHARINDEX('PHPatId:',String,0)-item2.Start),

[from PHPatId] = SUBSTRING(d.String,item3.Start,CHARINDEX('with Facility Id:',String,item3.Start)-item3.Start),

[to Facility Id] = SUBSTRING(d.String,item4.Start,CHARINDEX('Patient Id:',String,item4.Start)-item4.Start),

[to Patient Id] = SUBSTRING(d.String,item5.Start,CHARINDEX('PHPatId:',String,item5.Start)-item5.Start),

[to PHPatId] = SUBSTRING(d.String,item6.Start,LEN(d.String)-item6.Start+1)

FROM (

SELECT String = 'Merged Facility Id:0286- Patient Id:999000 PHPatId:28488 with Facility Id:0286-C Patient Id:124013 PHPatId:678839' UNION ALL

SELECT 'Merged Facility Id:029996- Patient Id:999999000 PHPatId:28999488 with Facility Id:0299986-C Patient Id:124999013 PHPatId:678999839'

) d

CROSS APPLY (SELECT Start = CHARINDEX('Facility Id:', String,0) + LEN('Facility Id:')) item1

CROSS APPLY (SELECT Start = CHARINDEX('Patient Id:', String,0) + LEN('Patient Id:')) item2

CROSS APPLY (SELECT Start = CHARINDEX('PHPatId:', String,0) + LEN('PHPatId:')) item3

CROSS APPLY (SELECT Start = CHARINDEX('Facility Id:', String,item1.Start) + LEN('Facility Id:')) item4

CROSS APPLY (SELECT Start = CHARINDEX('Patient Id:', String,item2.Start) + LEN('Patient Id:')) item5

CROSS APPLY (SELECT Start = CHARINDEX('PHPatId:', String,item3.Start) + LEN('PHPatId:')) item6

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 6

Sorry probably confused this, each row of data will look like this

id value

100 'Merged Facility Id:0286- Patient Id:999000 PHPatId:28488 with Facility Id:0286-C Patient Id:124013 PHPatId:678839'

I needed the data split going into another table which has 6 columns

so I needed the values split out, each row will look like the above. Your cross apply worked for 1st record not the second

0286- 999000 28488 0286-c 124013 678839