Results 1 to 4 of 4

Thread: Splitting up one string into three columns

  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Splitting up one string into three columns

    I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction?

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Here you go

    --drop table string
    use tempdb
    go
    create table string (a varchar(100))
    go
    insert into string select 'AA'
    insert into string select 'AA-AB'
    insert into string select 'AA-AB-AC'
    insert into string select 'AA-AB-ABC'
    insert into string select 'BB'
    insert into string select 'BB-BAA'
    insert into string select 'BB-BAA-BC'
    insert into string select 'BB-BAA-BD'
    insert into string select 'BB-BAA-BD-Q'
    insert into string select 'BB-BAA-BD-W'
    insert into string select 'CCC'
    insert into string select 'CCC-C'
    insert into string select 'DD'
    insert into string select 'DD-XX'
    insert into string select 'EE'
    go

    select a,level1= case charindex('-',a,1) when 0 then a else left(a,charindex('-',a,1) -1) end,
    level2=case when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) =0 then ''
    when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) <>0 then right(a,len(a)-charindex('-',a,1) )
    else substring(a,charindex('-',a,1) +1, charindex('-',a,charindex('-',a,1)+1) -charindex('-',a,1) -1) end,
    level3=case when charindex('-',a,1) =0 or charindex('-',a,charindex('-',a,1)+1) =0 then ''
    when charindex('-',a,charindex('-',a,1)+1) <>0 and charindex('-',a,1) <>0 and charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) =0 then right(a,len(a)- charindex('-',a,charindex('-',a,1)+1) )
    else substring(a,charindex('-',a,charindex('-',a,1)+1)+1 , charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) -charindex('-',a,charindex('-',a,1)+1)-1 ) end
    from string

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create view myview as
    select a,level1= case charindex('-',a,1) when 0 then a else left(a,charindex('-',a,1) -1) end,
    level2=case when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) =0 then ''
    when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) <>0 then right(a,len(a)-charindex('-',a,1) )
    else substring(a,charindex('-',a,1) +1, charindex('-',a,charindex('-',a,1)+1) -charindex('-',a,1) -1) end,
    level3=case when charindex('-',a,1) =0 or charindex('-',a,charindex('-',a,1)+1) =0 then ''
    when charindex('-',a,charindex('-',a,1)+1) <>0 and charindex('-',a,1) <>0 and charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) =0 then right(a,len(a)- charindex('-',a,charindex('-',a,1)+1) )
    else substring(a,charindex('-',a,charindex('-',a,1)+1)+1 , charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) -charindex('-',a,charindex('-',a,1)+1)-1 ) end
    from string

  4. #4
    Join Date
    Aug 2003
    Posts
    15

    Write a user defined function

    Hi,

    I think this is a classic requirement for a user defined function. Write a function similar to the split in vbscript. May be with an additional parameter to give u the correct element from the splitted string. For eg

    udf_split(string_to_be_split,element_index)

    this function can return the correct value.

    Say u have a string "ABC-DEF-HIJ" and u want to get DEF. The element_index can then be 2. This way u can split a string of unlimited number of elements and retrieve the correct element to insert into ur table!

    Let me know if this works
    Ketan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •