format a field in a select query

[algomes]

I want to format a field in a select query as this:

"select format(field,"00000&quot as field1"

Is there any way to do this in sql server?

 

[MaffewW]

hi, can you be a little more specific about what you are trying to do

 

[NathanGriffiths]

Hi, depends on what format you trying to go from and to.

example - Convert an integer 'IntField' into a 6 character varchar padded with '0':

Select Right('000000' + Convert(Varchar, IntField), 6)

if IntField = 897

this returns 000897

Note: this assumes the value of IntField is maximum of 999999!

Hope that helps,

Nathan

 

[algomes]

There is an alfanumeric field I want to format in the select query. The value is, for example "1234" and I want to get it as "01234" in the select process.

 

[denzillobo]

I think what you want to do is insert the value "00000" in a field in your table.

For this you can use the insert command.

Hope this helps.

DENZIL DENZIL

 

[NathanGriffiths]

I'm assuming by 'alfanumeric' the field is probably Varchar data type.

All you need to do in the select is;

Select right('00000' + ltrim(rtrim(<field_name>)), 5)

The ltrim(rtrim()) removes any spaces from around the field.
You are then adding a string of 0's onto the left of the field.
Then using right(,5) to truncate the field to 5 characters from the right which will leave your field padded with 0 from the left.

This will hopefully give you the result you're looking for.

cheers

Nathan