split a field value to three fields

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/

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources