Separate Value from one Column to 4 different Column - May be Substring issue?

  • 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

  • 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

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Thanks stewartc

    This going to be very handy..

    Thank You,

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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