locked
Search for columnName and Split data based on that column name RRS feed

  • Question

  • Hi All,

    I have a table with one column, lets say Notes of datatype varchar(max).

    Source Date:

    Notes

    ABC:123:XYZ Dept:IT NameID:1 Name:Tom Hummer Date:04/12/2004
    456789:CDEF:ADEF
    CBD:12/12/2000:ZXCV Dept:HR NameID:1 Name:Sam Dope Date:06/17/2005

    I want Output should look like below. It should split data at 'Dept:' and 'Name:' I need SQl code  for SQL Server 2008 R2

    Output:

    Notes

    Dept:IT Name:Tom Hummer
    Dept:HR Name:Sam Dope

    Thanks,
    RH


    sql

    Monday, June 16, 2014 8:24 PM

Answers

  • Hello,

    Please refer to the following statements:

    create table notes (note varchar(max));
    insert into notes values 
    ('ABC:123:XYZ Dept:IT NameID:1 Name:Tom Hummer Date:04/12/2004'),
    ('456789:CDEF:ADEF'),
    ('7890:RST:QWER Dept:Sales NameID:2 Name:Mike Kule'),
    ('CBD:12/12/2000:ZXCV Dept:HR NameID:1 Name:Sam Dope Date:06/17/2005')
    
    select case
             when charindex('Name:',note)>0 and charindex('Date:',note)>0
             then  SUBSTRING(note,charindex('Name:',note)-1,charindex('Date:',note)-charindex('Name:',note))
             when charindex('Name:',note)>0 and charindex('Date:',note)=0
             then  SUBSTRING(note,charindex('Name:',note)-1,len(note)-charindex('Name:',note))
          end  as name 
    from notes
    where charindex('Name:',note)>0 
    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Prashanth Jayaram Tuesday, June 17, 2014 2:10 PM
    • Marked as answer by Fanny Liu Monday, July 7, 2014 7:23 AM
    Tuesday, June 17, 2014 9:53 AM

All replies

  • Look into PATINDEX and SUBSTRING functions in BOL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 16, 2014 8:36 PM
  • Hi Naomi,

    I stucked with syntax..

    SeLect SUBSTRING(CAST(Notes AS VARCHAR(MAX)),charindex('Name:',CAST(Notes AS VARCHAR(MAX)))-1,len(CAST(Notes AS VARCHAR(MAX)))) as Name 

    With above syntax , I am getting output like this..but I don't want to show Date at the end of line.

    Notes
    Name:Tom Hummer Date:04/12/2004
    Name:Sam Dope Date:06/17/2005

    Thanks,

    RH


    sql

    Monday, June 16, 2014 9:27 PM
  • Some times my source data will not have Dates at the end of the line.

    Notes

    ABC:123:XYZ Dept:IT NameID:1 Name:Tom Hummer Date:04/12/2004
    456789:CDEF:ADEF
    7890:RST:QWER Dept:Sales NameID:2 Name:Mike Kule
    CBD:12/12/2000:ZXCV Dept:HR NameID:1 Name:Sam Dope Date:06/17/2005

    In above case my output should look like..

     Notes

    Dept:IT Name:Tom Hummer
    Dept:Sales Name:Mike Kule
    Dept:HR Name:Sam Dope

    Thanks,

    RH


    sql

    Monday, June 16, 2014 10:11 PM
  • Well, you should get a substring between Name and Date.

    When I have a complex parsing problem like this, I usually do it in steps using approach similar to outlined in this blog post

    Parsing the FullName field to individual components

    So, step by step using CROSS APPLY until you get the desired output.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 16, 2014 10:12 PM
  • Hello,

    Please refer to the following statements:

    create table notes (note varchar(max));
    insert into notes values 
    ('ABC:123:XYZ Dept:IT NameID:1 Name:Tom Hummer Date:04/12/2004'),
    ('456789:CDEF:ADEF'),
    ('7890:RST:QWER Dept:Sales NameID:2 Name:Mike Kule'),
    ('CBD:12/12/2000:ZXCV Dept:HR NameID:1 Name:Sam Dope Date:06/17/2005')
    
    select case
             when charindex('Name:',note)>0 and charindex('Date:',note)>0
             then  SUBSTRING(note,charindex('Name:',note)-1,charindex('Date:',note)-charindex('Name:',note))
             when charindex('Name:',note)>0 and charindex('Date:',note)=0
             then  SUBSTRING(note,charindex('Name:',note)-1,len(note)-charindex('Name:',note))
          end  as name 
    from notes
    where charindex('Name:',note)>0 
    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Prashanth Jayaram Tuesday, June 17, 2014 2:10 PM
    • Marked as answer by Fanny Liu Monday, July 7, 2014 7:23 AM
    Tuesday, June 17, 2014 9:53 AM