display 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)

display a string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-12 : 16:00:49

Kenny writes "Hi all,

I'm a beginner at SP. I've written some code to output a string vertically as follow:

declare @test varchar(40), @temp char(1), @len int
set @test = 'abc'
set @len = LEN(@test)
set @test = REVERSE(@test)
while @len > 0
begin
set @temp = substring(@test,@len,1)
print @temp
set @len = @len - 1
End
go

eg. 'abc'

display:
a
b
c

It works OK. But I would like to know if there's a better way to get the result.

Many thanks for any input."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-12 : 16:07:29

The code you have is fine, but there is a way to do this using a single SELECT statement.

Check this article:

http://www.sqlteam.com/item.asp?ItemID=5857

The key thing you need is a sequence table. If you set one up, you can use this also:

SELECT SubString(@test, Seq, 1) FROM Sequence WHEN Seq<=Len(@test)

The sequence table replaces the loop you wrote. As long as there are enough values to handle the longest string you need to pass it will work fine.

Check the links in the article for some more neat tricks you can use sequence tables for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources