,
Share this with your friends 
 

How to Split words in SQLSERVER

7 ratings Views 783 
Author: TEJINDER (TEJINDER SINGH)  View Profile |  View other solutions by this author

Question / Problem


How to Split words in SQLSERVER

Solution

declare @a varchar(100)
set @a = 'Tejinder Singh Barnala';
with tbl_for_csv as
(
select left(@a, charindex(' ',@a) - 1) as val,
stuff(@a +' ',1,charindex(' ',@a),'') as col
union all
select cast(left(col, charindex(' ',col) - 1) as varchar(100)),
stuff(col,1,charindex(' ',col),'') from tbl_for_csv
where col <> ''
)
select val from tbl_for_csv


/*another way*/

DECLARE @Item VARCHAR(max)
DECLARE @YourString1 VARCHAR(max)
DECLARE @xml xml
SELECT @YourString1 = 'tejinder singh barnala'
SET @YourString1 = N'' + replace(@YourString1,' ',' ') + ''
SELECT @YourString1 = @YourString1 + '' + ''
SELECT @xml = CONVERT(XML,@YourString1)
SELECT
t.value('.','varchar(max)') as [Name]
from @xml.nodes('//col') as a(t)

Applies to

Microsoft SQL Server 2005

Rank It

Login to rank it

Report


Advertisement