Search for columnName and Split data based on that column name

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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