Joshua Java

Adding extra char infront with SQL Server 2000

Posted on: June 13, 2008

Recently I’ve got a table with a field that the values needed to have an extra character infront of it. Each record must have extra zero infront of it and in the end the total character must be 10 characters long. So if I have a record with code: 12345, it should be transformed to 0000012345. In popular databases like PostgreSQL, MySQL or Oracle you would just call the LPAD() function. But unfortunately SQL Server 2000 hasn’t got it. Hopefully they already have it the latest version of SQL Server.

I couldn’t really find an elegant way to do this in SQL Server and so this is what I came up with:

select reverse(cast( reverse( cast(code as varchar) ) + '0000000000' as char(10) )) as productcode from product

  1. The first thing I did was to cast the record values as varchar using the cast() function to make sure that the data is in character data type.
  2. The next thing I did was to reverse the record values with the reverse() function.
  3. Then I add the extra zero and the end of the reversed record values.
  4. Then I cast the record as char(10) which would trim the un-needed zero behind the record values.
  5. After that I reverse the record values back with the reverse() function.

It’s a bit stupid, so if you think you’ve got a better way to do it in SQL Server, please feel free to share this with me.🙂

3 Responses to "Adding extra char infront with SQL Server 2000"

As per your explanation and my undersatnding is

Your prodcut code always need to show as 10 characters.
Steps:

1.Check the length
2.Based on the length proceed zero infront of code.

len(code)

“1” put 9 zeros “2” – 00000000 “3” -0000000 ..etc
Concat or “+” operator to add.

Hi,

You can use the following.

select left(‘0000000000’+’123’,10)

Result
——-
0000000123

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: