Select X number of characters?

[DarkWorlds]

As you may all know I am new, so forgive the dumb question.

I have a column that has data that varies in length of 3-5 characters long. Well I need to select the first 5, and if not it needs to add a 0 in front till it is 5 character long. How would I go about doing that?

 

[monksnake]

if your column of data is a varchar then do this:

Select right('00' + col1, 5) column1 from table1

otherwise you need to convert the column to a char first




<.

 

[bborissov]

Almost the same, but not exactly

Select right('00000' + CAST(col1 as varchar(5)), 5) column1 from table1

of course if the Col1 is already varchar there is no need to CAST it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mflancour]

I've always liked using:

Select REPLICATE('0', 5-LEN(col1 )) + CAST(col1 AS VARCHAR(5)) column1 FROM table1