SQL 2000 LPAD 1

[bugzLue]

Is there a function in SQL 2000 do do a LPAD like there is in ORACLE. I need to put Leading 000's in front of one of the columns to make it a total of 11 char. some have only 4 up to 11.

I need them from this
4978
125498
1238764982

to look like this
00000004978
00000125498
01238764982

Thank you
Greg

 

[tlbroadbent]

Use a combination of the STR and REPLACE functions.

STR parameters: number, length, after decimal
REPLACE parameters: <string value or column>, <string to replace>, <replacement string>

Example:
Select replace(str(483,11,0),' ','0')
Result: 00000000483
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bugzLue]

I need every entry in that column to have 11 numeric spaces. I need it to do it by defualt There is 87000 entrees, I cant do they one by one. Will this work fro the whole column?


I need them from this
to look like this
COLUMN_NAME MEMBERS COLUMN_NAME MEMBERS
4978 00000004978
125498 00000125498
1238764982 01238764982

 

[RickCole]

Yopu just need to change the statement to do all columns as follows:

Select replace(str(MEMBERS,11,0),' ','0') from tablename

Rick.

 

[tlbroadbent]

You can use this on any numeric column and select all the columns from all rows.

Example: select statement

Select
replace(str(Member,11),' ','0') As Member,
replace(str(SerialNo,11),' ','0') As SerialNo
From Table

Note: This will only work if the column is a numeric data type and you want to format it for display. If you intend to store the data with leading zeros, the data type must be character. A number is stored as anumber and will always display in the default format (no leading zeros) or as you explicitly format it in a select statement. Therefore, updating a numeric column with leading zeros is a wasted effort.

If the column is a character data type and you want to zero fill and store the data in that format, do the following.

Update table
Set Member=replace(space(11-len(Member))+ ltrim(rtrim(Member)),' ','0')

This is but one way to accomplish this. Someone may be able to suggest a more elegant solution. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bugzLue]

I have tried this and It still is not putting the leading zeros in front of the number. I have checked and it is numeric column 11,0. any more suggestion would be great fully accepted.

thank you
Greg

 

[tlbroadbent]

Could you post the query you tried?

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bugzLue]

Select
replace(str(Member,11),' ','0') As Member,

mxname,
mxstreet,
mxcsz

FROM mx

I also did it like

Select
replace(str(Member,11,0),' ','0') As Member,

mxname,
mxstreet,
mxcsz

FROM mx

Thank you for all of your help
Greg

 

[tlbroadbent]

Which version of SQL server do you run? Could you also post the result from either query? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bugzLue]

I am runnig SQL 2000 8.00.194
The result on both is the same

Select
replace(str(Member,11),' ','0') As Member,

mxname,
mxstreet,
mxcsz

FROM mx

RESULTS

MEMBER mxname mxstreet mxcsz
6789 Jonh Smith 1234 any where San Diego Ca 92108
6789 Jane Doe 1234 anywhere El Cajon Ca 92040
1234567890 Joe Jones 4321 somewhere someplace ca


I need to try and get the leading zeros.
Again thanks fro your help
Greg

 

[tlbroadbent]

I can't tell you why the code isn't working on your system. I've tested it on three servers, 2 versions of SQL Server, 3 compatibility levels and it worked each time.

Here is another way that may work. It does for me.

Select
right('0000000000'+ltrim(str(Member,11)),11) As Member,
mxname,
mxstreet,
mxcsz
FROM mx Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bugzLue]

I would like to thank Terry L. Broadbent for being such a big help I have it working now. I was not putting the space



Select replace(str(Member,11,0),' ','0') As Member,
SPACE ^ here

Thank you and everyone else who helped
Greg

 

[bugzLue]

I would like to thank Terry L. Broadbent for being such a big help I have it working now. I was not putting the space



Select replace(str(Member,11,0),' ','0') As Member,
SPACE ^ here

Thank you and everyone else who helped
Greg