String functions

Question

Post reply

String functions

PedroSimao

SSC Enthusiast

Points: 199
More actions
October 22, 2009 at 7:01 am

#215787

Hi all... I have a small problem and couldn't find the bloody answer...
I have this small loop:
DECLARE @cod INT
SET @cod = 0
WHILE (@cod <= 9)
BEGIN
PRINT @cod
SET @cod = @cod + 1
END
GO
That outputs:
0
1
2
3
4
5
6
7
8
9
Now, what I need to do is to output this:
000
001
002
003
004
005
006
007
008
009
It should be something like:
DECLARE @strValue VARCHAR
@strValue = '00' + @cod
but it is not :crazy:
Could anyone please help me out here?
Thanks in advance...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

I think this will work.

DECLARE @cod INT

DECLARE @strValue VARCHAR(5)

SET @strValue = '00'

SET @cod = 0

WHILE (@cod <= 9)

BEGIN

PRINT @strValue + CONVERT(VARCHAR,@cod)

SET @cod = @cod + 1

END

GO

PedroSimao SSC Enthusiast Points: 199 More actions · Answer 2

PedroSimao

SSC Enthusiast

Points: 199

October 22, 2009 at 7:08 am

#1069379

Thanks allot, it does indeed work!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Loops are slow... break the habit of using them. 😉

SELECT TOP 10 RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 AS VARCHAR(3)),3)

FROM Master.sys.All_Columns

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

good one Jeff. Can u explain this part - ORDER BY (SELECT 1). I looked up the order by clause but did not fully understand.

"Keep Trying"

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

ChiragNS (10/23/2009)
good one Jeff. Can u explain this part - ORDER BY (SELECT 1). I looked up the order by clause but did not fully understand.

Sure... at the minimum, the ROW_NUMBER function requires at least an ORDER BY clause. Since I don't actually care what the order is, I wanted to order by a constant. Since it won't actually allow a constant, I had to use a statement that created a constant instead. I supposed I could have used ObjectID (the PK from the All_Columns table) but thought that might confuse people into thinking that I cared about that column for some reason in the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Jeff Moden (10/23/2009)
ChiragNS (10/23/2009)
good one Jeff. Can u explain this part - ORDER BY (SELECT 1). I looked up the order by clause but did not fully understand.
Sure... at the minimum, the ROW_NUMBER function requires at least an ORDER BY clause. Since I don't actually care what the order is, I wanted to order by a constant. Since it won't actally allow a constant, I had to use a statement that created a constant instead. I supposed I could have used ObjectID (the PK from the All_Columns table) but thought that might confuse people into thinking that I cared about that column for some reason in the code.

Thanks

"Keep Trying"

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

By the way PedroSimao, do you understand why your approach didn't work, but Matt's did? If not, it'll probably happen to you again.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

PedroSimao SSC Enthusiast Points: 199 More actions · Answer 8

jcrawf02 (10/27/2009)
By the way PedroSimao, do you understand why your approach didn't work, but Matt's did? If not, it'll probably happen to you again.

yes jcrawf02.

I had tried several aproches converting INT to VARCHAR and add some chars to the string, but it just wasn't happening.

It was all a matter of sintaxe.

And Jeff Moden, thanks allot for the information!