Let’s say you have a varchar field on a SQL table that is a length of 10 and it contains only integers.
If the value of one of the fields is 5, it is stored as ‘5’, not ‘0000000005’. This creates a problem if you want to sort your values. To have these fields sorted properly, you need to pad the left with zeroes in your SQL statement.
For example, you want your result set to like this:
- 0000001234
- 0000000005
- 0005694859
- 0000000001
Sorted, it would like this:
- 0000000001
- 0000000005
- 0000001234
- 0005694859
If you didn’t pad with zeroes in your SQL statement, your result set would look like this:
- 1
- 5
- 1234
- 5694859
Sorted, it would like this:
- 1
- 1234
- 5
- 5694859
That is no good. It should be:
- 1
- 5
- 1234
- 5694859
You can accomplish this by using the RIGHT and RTRIM method.
Here’s how:
SELECT RIGHT('000000000' + RTRIM(*column-name*), 10) from *sql-table* order by *column-name*
As stated above, the column of our table is defined with a length of 10. One of the rows has in the column a value of ‘1234’, but would actually received in as ‘1234 ‘.
RTRIM says strip off trailing spaces. The result of RTRIM(*column-name*) is then ‘1234’.
The RIGHT method says, of the combined string of ‘00000000001234’ (The column value already being stripped of the trailing spaces), give me only a subset of the combined string (currently 14 in length) only 10 characters starting from the right. As a result, the first 4 zeroes are stripped off resulting in ‘0000001234’.
Test these different SQL statements and check out the results:
SELECT *column-name* from *sql-table* order by *column-name*
(Result = '1234 ')
SELECT RTRIM(*column-name*) from *sql-table* order by *column-name*
(Result = '1234')
SELECT RIGHT(RTRIM(*column-name*), 10) from *sql-table* order by *column-name*
(Result = '1234 ')
SELECT RIGHT('000000000' + RTRIM(*column-name*), 10) from *sql-table* order by *column-name*
(Result = '0000001234')