How Do I Add leading Zeros to nvarchar field in query? 3

[Reynet01]

I have a field called SID it is a maximum of 8 characters I need to add leading zeros to the front of the text if it is not 8 characters long Example: 1234 needs to be 00001234 How is this done so that it pulls data in the correct format in a sql query? Thanks for any help you can give.

If knowledge were power I would be a AAA battery!

 

[gmmastros]

Since it is a char field, you can add zero's to the beginning of the field.

Select Right('00000000' + SID, 8)
From table

Basically, you add 8 zero's to the front of the string, then take the last 8 characters.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Reynet01]

Thanks it works great, so simple but so far out of my knowledge grasp

If knowledge were power I would be a AAA battery!

 

[Reynet01]

OOPs it still shows the numbers out of order is there any way to have this convert it to a number

If knowledge were power I would be a AAA battery!

 

[SQLDenis]

maybe you need to RTRIM it

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

... and probably (but much less likely) also LTRIM it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Reynet01]

Here is what I have for the statement

Select Right('00000000' + SID, 8)
From [dbo].[Users]
Order By SID Desc

information is stored as charecters. The above statement adds the leading zeros as it should but I need to sort these values in order and since they are stored as text they still show out of order Like below

00009999
00000002
00001235
00123456
00012345
00001234
00011111
00001111

I guess since it is sorting a char field that is the reason it is still out of order.



If knowledge were power I would be a AAA battery!

 

[jbenson001]

It is because you are still sorting on the ORIGINAL column. You need to sort on your new derived column.

Try:

Order By Right('00000000' + SID, 8)

Jim

 

[SQLDenis]

order by convert(int,sid)

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[roccorocks]

Or he could change the field from CHAR(8) to Varchar(8)! RTRIM would do the trick too (in case your wondering Reynet, you have 8 - Len(sid) of space padded to the right when using a CHAR datatype).

Rocco

 

[SQLDenis]

actually
order by convert(int,rtrim(sid))

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[roccorocks]

I guess my post is insignificant now.

Rocco

 

[Reynet01]

Thanks you guys are a life saver

If knowledge were power I would be a AAA battery!

 

[Reynet01]

Sorry roccorocks your code works as well I just tried SQLDenis's post first and it achieved my goal.

If knowledge were power I would be a AAA battery!