Replace with newline - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Replace with newline

Author

Topic

TheOne
Starting Member

6 Posts

Posted - 2009-05-12 : 06:33:30

how to replace some character with newline

example
1> select 'abcf afga 7865'
output expected
abcf
afga
7865

or 2> select 'abcf,afga,7865'
output expected
abcf
afga
7865

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-12 : 06:45:28

is this u want
declare @str1 varchar(max)
set @str1= 'abcf afga 7865'

SELECT
SUBSTRING(@str1,charindex(' ',@str1,v.number),abs(charindex(' ',@str1,charindex(' ',@str1,v.number)+1)-charindex(' ',@str1,v.number)))as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(' ' + @str1, v.number, 1) = ' '

declare @str varchar(max)
set @str= 'abcf,afga,7865'

SELECT
replace(SUBSTRING(@str,charindex(',',@str,v.number),abs(charindex(',',@str,charindex(',',@str,v.number)+1)-charindex(',',@str,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str)
AND substring(',' + @str, v.number, 1) = ','