How to split the values in sqlserver 2008

Posted by Nandkishorre under Sql Server on 10/3/2012 | Points: 10 | Views : 2244 | Status : [Member] | Replies : 6
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 .




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
@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

Up
0
Down
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

Up
0
Down
@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

Up
0
Down
I have solved this my self.

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

Login to post response