Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split a field value to three fields

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-11-11 : 14:53:00
Hi friends,

I have a table Customer in the following way

CustomerNumber CustmerType

aaa1 1-Regular,2-Daily,3.5-Yearly
bbb1 0.5 Regual, 1.5-daily, 4-Yearly

Now i want to split the Customer Type into The fields
Regular, Daily and Yearly for the Customer number and show the values..


How can i achieve it...Thank you in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 15:52:12
try the string splitter function: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-11-11 : 17:43:55
Thank You gbritton but i could achieve my target using the Query below: Ofcourse i Customized it to my requirements..so anyone looking for similar question please try this..

;WITH Split_Names (Value,Name, xmlname)
AS
(
SELECT Value,
Name,
CONVERT(XML,'<Names><name>'
+ REPLACE(Name,',', '</name><name>') + '</name></Names>') AS xmlname
FROM tblnames
)

SELECT Value,
xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name,
xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname
FROM Split_Names
Go to Top of Page
   

- Advertisement -