Padding with Leading Zeros

How do you pad a string in SQL with leading Zeros (0)?

Sometimes you have numbers like:

0
2
20
30
350

If you want the numbers to all line in and pad with zeros then you can use the REPLICATE function in SQL Server to accomplish this:

REPLICATE('0', 3 - LEN(YOUR_FIELD))

The 3 in the statement above is how many characters should be.  After running the REPLICATE, you should get this:

000
002
020
030
350

Plus, they are now easier to sort.

VAL Function in SQL Server

Is there a something similar to the VAL function in SQL Server?

I had someone complain that they could not sort on the value of a field and that their sort was wrong.  They sent me this list: (ID)

8, 10, 11, 20, 30, 45, 9.

When they did the ORDER BY ID, the list came out like this;

10, 11, 20, 30, 45, 8, 9

It looks like their ID is a character and hence ASCII sorting will take place but if they write the SQL query like this;

SELECT ID from TblAccounts
ORDER BY CAST(ID as Int)

The result is then as expected 8, 9, 10, 11, 20, 30, 45.

Programmers are familiar with VAL("some number string") in access or Visualbasic. The SQL CAST or CONVERT should work.