June 2, 2010 at 12:42 pm
I have data which resembles below
declare @msg varchar(200)
set @msg = 'Merged Facility Id:6644- Patient Id:072505 with Facility Id:6645- Patient Id: 072506'
What I need to is to strip out the Merged Facility ID (the '6644-') the 1st Patient ID
'072505' and the Facility ID (the '6645-') and the second patient ID ('072506') The Facid's will always be 5-6 characters, the Patient ID will be between 5-12 characters.
Would a substring be the best approach?
June 2, 2010 at 3:10 pm
If I'm understanding this correctly, you want to just remove all numbers and hyphens? (0-9 and "-")
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2010 at 3:12 pm
No I need to be able to manipulate the first facid and the first patientid and then the second facid and second patid
June 2, 2010 at 3:22 pm
timscronin (6/2/2010)
No I need to be able to manipulate the first facid and the first patientid and then the second facid and second patid
Okay, then based upon the string that you provided in the first post, what do you want the result to be?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2010 at 8:29 pm
I sense the OP needs 4 columns out of the string, as in, FirstFacilityID ,FirstPatieintID, SecondFacilityID, SecondPatieintID ; these 4 columns will hold only intergers, stripped from the real string..
June 2, 2010 at 8:34 pm
Tim, will your string have only two facility IDs and 2 patient IDs always? Will the format of the string change from row to row..
Please post us sample data in readily consumable format and as Wayne suggested, post your desired result in some visual representation... This will remove a lot of doubts..
June 2, 2010 at 9:05 pm
ColdCoffee (6/2/2010)
Please post us sample data in readily consumable format and as Wayne suggested, post your desired result in some visual representation... This will remove a lot of doubts..
What ColdCoffee means is.... read the first link in my signature and please do what it asks.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2010 at 9:17 pm
ColdCoffee (6/2/2010)
I sense the OP needs 4 columns out of the string, as in, FirstFacilityID ,FirstPatieintID, SecondFacilityID, SecondPatieintID ; these 4 columns will hold only intergers, stripped from the real string..
Assuming that this is what the OP wants, then this code should work:
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://qa.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test-2 TABLE (
ID int IDENTITY,
Col1 varchar(max)
)
INSERT INTO @test-2
SELECT 'Merged Facility Id:6644- Patient Id:072505 with Facility Id:6645- Patient Id: 072506'
;WITH CTE AS
(
-- first, get the first two columns.
SELECT Col1,
Pos1 = CharIndex('Merged Facility Id:', Col1),
Pos2 = CharIndex('Patient Id:', Col1)
FROM @test-2
), CTE2 AS
(
-- now, get the next two columns. (Need the first two columns in
-- order to start after that to get the same names for the next two.)
SELECT Col1,
Pos1,
Pos2,
Pos3 = CharIndex('Facility Id:', Col1, Pos2),
Pos4 = CharIndex('Patient Id:', Col1, Pos2+2)
FROM CTE
)
-- get the columns
SELECT 'Merged Facility ID' = SubString(Col1, Pos1 +19, Pos2-Pos1-20),
'First Patient ID' = SubString(Col1, Pos2+11, Pos3-Pos2-17),
'Facility ID' = SubString(Col1, Pos3+12, Pos4-Pos3-13),
'Second Patient ID' = SubString(Col1, Pos4+12, LEN(Col1)-Pos4-11)
FROM CTE2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 7:34 am
Yes always 2 facid and 2 patid, it is a merge from facid and patid (1st two) to 2nd facid and patid
June 3, 2010 at 10:42 am
Assumptions:
1. "-" hyphens separate merged facility , (patient2 facility2) and patient 1
2. "with" can separate patient2 from facility2
3. ids can be variable length and are separated from label by ":"
declare @msg varchar(200)
set @msg = 'Merged Facility Id:6644- Patient Id:072505 with Facility Id:6645- Patient Id: 072506'
select convert(int,FacilityId1) as FirstFacilityID
, convert(int,substring(Msg, charindex(':',Msg)+1 ,200)) as FirstPatieintID -- correct spelling ? -> FirstPatientID
, convert(int,FacilityId2) SecondFacilityID
, convert(int,PatientId2) SecondPatieintID -- correct spelling ? -> SecondPatientID
from (
select FacilityId1
, PatientId2
,substring(Msg, charindex(':',Msg)+1 ,charindex('-',Msg+'-')-charindex(':',Msg)-1) as FacilityId2
,substring(Msg,charindex('-', Msg+'-')+1,200) as Msg
from (
select FacilityId1
,substring(Msg, charindex(':',Msg)+1 ,charindex(' with ',Msg+' with ')-charindex(':',Msg)-1) as PatientId2
,substring(Msg,charindex(' with ', Msg+' with ')+1,200) as Msg
from (
select
substring( @msg, charindex(':',@msg)+1 ,charindex('-',@msg+'-')-charindex(':',@msg)-1) as FacilityId1
,substring(@msg,charindex('-', @msg+'-')+1,200) as Msg
) T
) T
)T
June 4, 2010 at 4:14 am
This might also be a good place to start.
DECLARE @msg nvarchar(max)
set @msg = 'Merged Facility Id:6644- Patient Id:072505 with Facility Id:6645- Patient Id: 072506'
Select replace(replace(replace(replace(@msg, ' Patient Id: ', ','), 'Merged Facility Id:', ''), ' with Facility Id:', ','), ' Patient Id:', ',')
Splitting result on comma can be tally-table driven
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply