Parsing a string - 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 2000 Forums

SQL Server Development (2000)

Parsing a string

Author

Topic

taylo
Yak Posting Veteran

82 Posts

Posted - 2002-06-18 : 12:58:54

I have kind of an odd request. Has anyome ever written
a script that will parse a string letter by letter?
What I am looking to is as follows

Dim s,t as String
s = "cheese"
For i = 0 to Length(s)
t = t & s(i) & '<BR>'
NEXT

Obvioulsy I want to insert a break statement after each letter
so the word appears in a column.
Now I could easily do this in ASP but since I am using ASP.NET and binding to a control it's just not practical. So I have opted to
try this in an SP.

Thanks in advance,

Rob

dsdeming

479 Posts

Posted - 2002-06-18 : 13:09:07

Try something like this:

DECLARE @s varchar( 20 ), @t varchar( 40 ), @i int
SET @s = 'cheese'
SET @i = 1

WHILE @i <= LEN( @s )
BEGIN
SET @t = @t + SUBSTRING( @s, @i, 1 ) + '<BR>'
SET @i = @i + 1
END

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-06-18 : 13:14:12

You can do this by using the string manipulation functions of T-SQL and the WHILE loop. Another way is to use a numbers table. Here's an example:

DECLARE @output sysname, @input sysname, @len tinyint
SET @output = ''
SET @input = 'cheese'
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) + '<BR>' AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
) Derived

SELECT @output

For more info, read this latest article of mine: http://www.sqlteam.com/redir.asp?ItemID=9629

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources