Adding extra char infront with SQL Server 2000
Posted June 13, 2008on:
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
- 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.
- The next thing I did was to reverse the record values with the reverse() function.
- Then I add the extra zero and the end of the reversed record values.
- Then I cast the record as char(10) which would trim the un-needed zero behind the record values.
- 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. 🙂