Padding a string with zeros

[Greg Harrington]

Under Sybase 4.92 how can I pad a string with zeros. As an example
SELECT lpad(acctnum,6) from .... (where acctnum is an int) would return
something like 000506 with zeros padded on the left. Any solution at all
would be *great*, even if it is more complex than my made-up function
"lpad".

[Rex_Wheele...@hf.ccm.intel.com]

Try something like:

declare @bob char(20)
declare @len int
select @bob = "123",@len = 10

select replicate("0",@len-datalength(rtrim(@bob)))+@bob

If your string is varchar() you don't need the rtrim().

Rex

[Mischa Sandberg]

In article <2q4ici$c...@nkosi.well.com>, g...@well.sf.ca.us (Greg Harrington)
writes:
>
> Msg-ID: <2q4ici$c...@nkosi.well.com>
> Posted: 3 May 1994 04:02:26 GMT
>
> Org. : The Whole Earth 'Lectronic Link, Sausalito, CA

>
>
>
> Under Sybase 4.92 how can I pad a string with zeros. As an example
> SELECT lpad(acctnum,6) from .... (where acctnum is an int) would return
> something like 000506 with zeros padded on the left. Any solution at all
> would be *great*, even if it is more complex than my made-up function
> "lpad".
>
>

Didn't someone just ask this one?
SELECT RIGHT("00000"+CONVERT(VARCHAR,ABS(acctnum)), 6) ...
... unless you don't need the "ABS" function.

BTW, it's really a help to graft an M4 preprocessor onto your SQL tool.
Then you can do junk like:

define(`LPAD', `right(replicate("0",$2)+convert(varchar,$1),$2)')

select LPAD(acctnum,6) from mytable; etc.

It would be nice if the server supported scalar functions, but if you
don't mind thinking in FP terms, M4 ain't a bad second option.
--
Mischa Sandberg ... Mischa_...@mindlink.bc.ca
or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
*-*-*-*-*-*-*-*-*-*-*
Engineers think equations are an approximation of reality.
Physicists think reality is an approximation of the equations.
Mathematicians never make the connection.

[Mischa Sandberg]

In article <2q6114$a...@ornews.intel.com>,

Rex_Wheele...@hf.ccm.intel.com writes:
>
> > Under Sybase 4.92 how can I pad a string with zeros

> Try something like:
>
> declare @bob char(20)
> declare @len int
> select @bob = "123",@len = 10
>
> select replicate("0",@len-datalength(rtrim(@bob)))+@bob

Careful: if rtrim(@bob) is "" (null), then @len-datalength(...) is
also null.

[Greg Harrington]

n> ...graft an M4 preprocessor onto your SQL tool...
Could someone explain this for the "less informed". :-)

thanks

[Joel Friedman]

Here's something that I used to pad with '0's:

SELECT right("000000" + convert(varchar(6), number), 6)

In this case, number can be up to 6 digits long.

---
====================================================================
|
_/ _/_/_/ _/_/_/ _/ | Joel Friedman (consultant)
_/ _/ _/ _/ _/ |
_/ _/ _/ _/_/ _/ | - "If you've got a solution, let me
_/ _/ _/ _/ _/ _/ | find the problem."
_/_/ _/_/_/ _/_/_/ _/_/_/ |
| W: (202) 752-2210
====================================================================

[Tony Langdon]

You could use the following sql:

SELECT REPLICATE("0",@length - FLOOR(LOG10(acctnum))) + CONVERT(char,acctnum)

where @length is the required field width (6 in your case)

---
T.Langdon | Email : lang...@jeeves.fi.gs.com
Goldman Sachs International Limited | Phone : +44 71 774 5420
Peterborough Court, 133 Fleet Street, |
London. EC4B 2AA | ... and the Reds go marching on, on, on ...

[Stanley B. Wu]

Try this:

declare @value int
declare @length int
select @value = 56
select @length = 6
select replicate("0",@length-char_length(ltrim(str(@value))))+ltrim(str(@value))

In article c...@nkosi.well.com, g...@well.sf.ca.us (Greg Harrington) writes:
>
>