How to split the values in sqlserver 2008

Posted by Nandkishorre under Sql Server on 10/3/2012 | Points: 10 | Views : 3251 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

Vessl Name:PAC BINTAN,
Voyage No:01,
IGM No.:C-95/12,
EGM No.:C-108/12,
Type of Voyage - Coastal/foreign:COASTAL RUN,
Port of Call :VISAKHAPATNAM,
Clients Name:LEIGHTON CONTRACTORS(INDIA)LTD

I select data from the table as above.Then, How to split the above underline values and return in a table(Like below) form in sqlserver 2008.

Result:

PAC BINTAN
01
C-95/12
C-108/12
COASTAL RUN
VISAKHAPATNAM
LEIGHTON CONTRACTORS(INDIA)LTD

if any one know about this tell me.

Regards
Nanda Kishore.CH .

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Divyaselvarathinam on: 10/3/2012 [Member] Starter | Points: 25

0	What about the vesselname,voyage no... If those are the column names and u have to split that Coastal/foreign:COASTAL RUN as COASTAL RUN alone then use the below code: declare @voyage varchar(50) declare @ind int ---columnname in which Coastal/foreign:COASTAL RUN is stored--- select @voyage =columnname from TableName set @ind=Charindex(':', @voyage ) select RIGHT((@voyage ),LEN@voyage )-@ind) Otherwise u can use the same for all the values to split them after the delimited character':' Divya Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

What about the vesselname,voyage no... If those are the column names and u have to split that Coastal/foreign:COASTAL RUN as COASTAL RUN alone then use the below code:

declare @voyage varchar(50)

declare @ind int

---columnname in which Coastal/foreign:COASTAL RUN is stored---
select @voyage =columnname from TableName

set @ind=Charindex(':', @voyage )
select RIGHT((@voyage ),LEN@voyage )-@ind)

Otherwise u can use the same for all the values to split them after the delimited character':'

Divya

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.Biswas on: 10/4/2012 [Member] Platinum | Points: 25

0	Declare @t table (Value Varchar(100)) Insert Into @t Values ('Vessl Name:PAC BINTAN'),('Voyage No:01'),('IGM No.:C-95/12'), ('EGM No.:C-108/12'),('Type of Voyage - Coastal/foreign:COASTAL RUN'), ('Port of Call :VISAKHAPATNAM'),('Clients Name:LEIGHTON CONTRACTORS(INDIA)LTD') SELECT Result = SUBSTRING(Value,PATINDEX('%:%',Value)+1,LEN(Value)) FROM @t Result ---------- PAC BINTAN 01 C-95/12 C-108/12 COASTAL RUN VISAKHAPATNAM LEIGHTON CONTRACTORS(INDIA)LTD Best Regards, Niladri Biswas Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Declare @t table (Value Varchar(100))

Insert Into @t Values

('Vessl Name:PAC BINTAN'),('Voyage No:01'),('IGM No.:C-95/12'),

('EGM No.:C-108/12'),('Type of Voyage - Coastal/foreign:COASTAL RUN'),

('Port of Call :VISAKHAPATNAM'),('Clients Name:LEIGHTON CONTRACTORS(INDIA)LTD')



SELECT Result = SUBSTRING(Value,PATINDEX('%:%',Value)+1,LEN(Value))

FROM @t 



Result

----------

PAC BINTAN

01

C-95/12

C-108/12

COASTAL RUN

VISAKHAPATNAM

LEIGHTON CONTRACTORS(INDIA)LTD

Best Regards,
Niladri Biswas

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 10/5/2012 [Member] Starter | Points: 25

0	@Niladri.Biswas Hi worked wit your coding..It worked...Can you explain me whats the special in patindex..why cant use normal index for substring operation Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nandkishorre on: 10/5/2012 [Member] Starter | Points: 25

0	I have lakhs of records in my table.. it takes so much time.. thats y i want to write function in server side. Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Niladri.Biswas on: 10/5/2012 [Member] Platinum | Points: 25

0	@Oswaldlily, what is the "Normal index " that will do the sub-string operation? Can you please explain with some example Best Regards, Niladri Biswas Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nandkishorre on: 10/6/2012 [Member] Starter | Points: 25

0	I have solved this my self. Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts