January 27, 2011 at 1:07 pm
Hi everyone,
I have situation where i need to separate value from one column to four different column. I have provide you a sample script below which will help you to understand my needs but here is what I am trying to accomplish.
My goal is to i need to keep first five digit from my Procedure_Code column and any value after each '-' (dash) need to go according to its order. For Example: if i have value 50534-25 than first five digit will go into my procedure_code column and 25 will go to my "Modify1" column. Now, if i have 50534-25-26-27 than first five will be in Procedure_code column and 25 will be in modify1, 26 will be in modify2 and 27 will be in modify3.
Please help me out to with my script. Please let me know where should i need to modify my script.
Drop Table #myProc
create table #myProc
(
id int identity(1,1),
procedure_code varchar(20)
)
insert into #myProc select '4001F'
insert into #myProc select '50534-25'
insert into #myProc select '50534-26-25'
insert into #myProc select '50534-25-26-27'
insert into #myProc select '50534-25-27-28-29'
insert into #myProc select '4046F1P'
insert into #myProc select '8046F-2P'
insert into #myProc select '90990R'
insert into #myProc select '99029QW'
insert into #myProc select '89099-QW'
select * from #myProc
select a.id
,a.Procedure_Code
,substring(a.Procedure_Code,1,5) as Stnd_Procedure_code
,case
when len(a.procedure_code) = 7 and substring(a.procedure_code,6,2) IN ('1P','2P','8P')
then substring(a.procedure_code,6,2)
when len(a.procedure_code) = 8 and substring(a.procedure_code,6,3) IN ('-1P','-2P','-8P')
then substring(a.procedure_code,7,2)
when len(a.procedure_code) = 6 and substring(a.procedure_code,6,1) IN ('R')
then substring(a.procedure_code,6,1)
when len(a.procedure_code) = 7 and substring(a.procedure_code,6,2) IN ('QW')
then substring(a.procedure_code,6,2)
when charindex( '-',a.procedure_code) != 0
then substring(a.procedure_code, charindex( '-',a.procedure_code)+1, 2)
end AS Modifier1,
case when len(a.procedure_code) = 11 then substring(a.procedure_code,10,2) end as Modifier2,
case when len(a.procedure_code) = 14 then substring(a.procedure_code,13,2) end as Modifier3,
case when len(a.procedure_code) = 17 then substring(a.procedure_code,16,2) end as Modifier4
From #myProc a
January 27, 2011 at 1:18 pm
Found my Solution. Here is my final Script...have a fun..
Resolution: Need to add IN clause with my length when i look for Modifer2,3,4.
Drop Table #myProc
create table #myProc
(
id int identity(1,1),
procedure_code varchar(20)
)
insert into #myProc select '4001F'
insert into #myProc select '50534-25'
insert into #myProc select '50534-26-25'
insert into #myProc select '50534-25-26-27'
insert into #myProc select '50534-25-27-28-29'
insert into #myProc select '4046F1P'
insert into #myProc select '8046F-2P'
insert into #myProc select '90990R'
insert into #myProc select '99029QW'
insert into #myProc select '89099-QW'
select * from #myProc
select a.id
,a.Procedure_Code
,substring(a.Procedure_Code,1,5) as Stnd_Procedure_code
,case
when len(a.procedure_code) = 7 and substring(a.procedure_code,6,2) IN ('1P','2P','8P')
then substring(a.procedure_code,6,2)
when len(a.procedure_code) = 8 and substring(a.procedure_code,6,3) IN ('-1P','-2P','-8P')
then substring(a.procedure_code,7,2)
when len(a.procedure_code) = 6 and substring(a.procedure_code,6,1) IN ('R')
then substring(a.procedure_code,6,1)
when len(a.procedure_code) = 7 and substring(a.procedure_code,6,2) IN ('QW')
then substring(a.procedure_code,6,2)
when charindex( '-',a.procedure_code) != 0
then substring(a.procedure_code, charindex( '-',a.procedure_code)+1, 2)
end AS Modifier1,
case when len(a.procedure_code) IN ('11','14','17') then substring(a.procedure_code,10,2) end as Modifier2,
case when len(a.procedure_code) IN ('14','17') then substring(a.procedure_code,13,2) end as Modifier3,
case when len(a.procedure_code) IN ('17') then substring(a.procedure_code,16,2) end as Modifier4
From #myProc a
January 28, 2011 at 12:23 am
This was removed by the editor as SPAM
January 28, 2011 at 12:25 am
This was removed by the editor as SPAM
January 28, 2011 at 4:08 am
Thanks stewartc
This going to be very handy..
Thank You,
January 28, 2011 at 7:10 am
Instead of using a while loop, a better performing, set-based method is to use the DelimitedSplit8K function. Click here for the latest Delimited Split Function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply