CAST Integer to Character with leading zeroes

Question

Post reply

CAST Integer to Character with leading zeroes

roy.tollison

SSCommitted

Points: 1930
More actions
August 21, 2008 at 2:39 pm

#211480

Currently I am using the following to take an integer and create a 6 character number with leading 0's but really need a shorter method/function.
REPLICATE(0, 6-len(CONVERT(varchar(6), integerfield1))) + CONVERT(Varchar(6), integerfield1)
to create a character field of
000001 for the value of 1 and
000010 for the value of 10 and so on...
Is there a shorter method/function for creating these results.
I absolutely need it to be a left justified number with leading zeroes because i and concatenating a string, number, sting and comparing it to a given string.

Viewing 11 posts - 1 through 10 (of 10 total)

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

How about this, not sure how short you want it

DECLARE @Int AS INT

SET @Int = 1

SELECT RIGHT(REPLICATE('0', 6) + CAST(@Int AS VARCHAR(6)), 6)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Even shorter:

DECLARE @Int AS INT

SET @Int = 1

SELECT RIGHT('00000' + CAST(@Int AS VARCHAR(6)), 6)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 3

Nice.. I almost did it without the REPLICATE() function. Always felt it was more typing than needed. 🙂

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

dmc (8/21/2008)
Nice.. I almost did it without the REPLICATE() function. Always felt it was more typing than needed. 🙂

There is something wrong with a convenience function to make duplicates of a sinlge character that takes 12 extra characters to do it. For anything less than 13 copies of a character, you might as well just type it in as a literal. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Short? You guys need to try harder. :satisfied:

select

NewNum = right(1000000+MyNum,6)

from

( --Test Data

select MyNum=1union all

select 10union all

select 100union all

select 1000union all

select 10000union all

select 100000union all

select 999999 ) a

Results:

NewNum

------

000001

000010

000100

001000

010000

100000

999999

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 6

HA! Short indeed.. but the post asked for a character based result. But koodos if the string data type is not really needed.

roy.tollison SSCommitted Points: 1930 More actions · Answer 7

Thank you for that. I am using a 4GL language and they only allow me 300 characters for my part of the query. So when i have 3-5 selection criterias i usually have to decide which ones to leave out. This will help tremendously....

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Can you write stored procedures and/or functions on the database?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

roy.tollison SSCommitted Points: 1930 More actions · Answer 9

We can write functions/stored procedures but the problem comes from the 4GL Language.

THEY issue a query command (SELECT ..... WHERE... ) and we are only allowed to add to the where clause. Plus I am limited to just 300 characters. Most of my selection criteria is for ranges of values. strings, dates, integers. But on some of the selection routines it involves 3 fields in multiple tables

F1 Char(4), F2 Integer, F3 Integer. through our programs we limit F2 to max size of 6 digits (999999) and F3 to 2 digits (99). So wen i put those into my where clause with the big replicate command i pretty much ate up the 300 characters.

F1+F2(6)+F3(2) in (select F1+F2(6)+F3(2) from Table2 where F4 = ...) or

F1+F2(6)+F3(2) >= '{programed start value (string)}' and F1+F2(6)+F3(2) <= '{programmed end value (string)}'

So any thing i could do to shorten it helps.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

dmc (8/22/2008)
HA! Short indeed.. but the post asked for a character based result. But koodos if the string data type is not really needed.

You do realize that the RIGHT function returns a string?

My code simply takes advantage of the fact that the integer expression is automatically cast to a character string.

From SQL Server 2000 Books Online:

[font="Arial Narrow"]RIGHT

Returns the part of a character string starting a specified number of integer_expression characters from the right.

Syntax

RIGHT ( character_expression , integer_expression )[/font]