Concatenate string in loop - 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)

Concatenate string in loop

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-03-17 : 06:41:50

Hi, i have below piece of code.

Declare @i int
set @i = 96
While(@i < 100)
Begin
set @i = @i +1
Select 'sat' + char(@i)
End

I am getting output as
sata
satb
satc
satd

Instead of this, i am looking for output as
satabcd

I tried this way..but now luck
Declare @i int
Declare @finalstr varchar(30)

set @i = 96
While(@i < 100)
Begin
set @i = @i +1
Set @finalstr = @finalstr + 'sat' + char(@i)
End
Select @finalstr

what change i need to do in my code?

developer :)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-17 : 06:49:00

Almsot there:


Declare @i int
Declare @finalstr varchar(30)

SET @finalStr = 'sat'
set @i = 96
While(@i < 100)
Begin
set @i = @i +1
Set @finalstr = @finalstr  + char(@i)
End
Select @finalstr

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 06:49:55

[code]
select @finalstr = isnull(@finalstr, 'sat') + char(@i)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 06:50:22

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-03-17 : 07:00:12

Its fine. If there is no intial data to append..How can i do? I mean..how the following code to be execute.. I am getting NULL value as output

Declare @i int
Declare @finalstr varchar(30)

set @i = 96
While(@i < 100)
Begin
set @i = @i +1
Set @finalstr = @finalstr + char(@i)
End
Select @finalstr

quote:
Originally posted by Transact Charlie

Almsot there:

Declare @i int
Declare @finalstr varchar(30)

SET @finalStr = 'sat'
set @i = 96
While(@i < 100)
Begin
set @i = @i +1
Set @finalstr = @finalstr + char(@i)
End
Select @finalstr

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

developer :)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-17 : 07:10:17

Huh?

Either use khatan's suggestion or mine.

My way initialises the variable first before the loop with a value (could be the empty string if you wanted. Khatan's checks for a null in every loop and substitutes for a value.

If you add anything to NULL you get NULL.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 07:10:58

"Set @finalstr = @finalstr + char(@i)"

You are concatenating char(@i) to NULL (value of @finalstr) because you haven't initialised @finalstr, nor used IsNull - both suiggested in the replies above

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 07:11:29

[code]select @finalstr = isnull(@finalstr, '') + char(@i)[/code]

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-03-17 : 07:15:20

Thank you Charlie

developer :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources