putting '0' to number 1-9

[icebo3]

in sql server, what is the sql script or function to assign a '0' to number from 1-9 ?? example when output is 2 is should change it to 02. if 12 then it is 12

 

[JamesLean]

SELECT RIGHT('0' + CAST(mycol AS varchar), 2)

--James

 

[icebo3]

thks ....i understand what u are doing ... u are actually put a '0' to the mycol and then use a right trim to take the back 2 only so if '1' then then '01'... if '12' then '012' and right trim will be '12' right ???

since i am using it as a date function , it will work as until month 12.... what happen if i am not using it as date but normal character like qty .... exp '123' or '400' then this function might not work .... what other function can i work as i will be generating this record and then sometime need to sort by qty ... any other function???

 

[JamesLean]

Sorry, I don't quite follow. Are you saying you have a list of numbers that could be any length and you want to return them, but add a leading zero to any single-digit numbers? If so, then you could use:

SELECT CASE LEN(mycol)
  WHEN 1 THEN '0' + CAST(mycol AS varchar)
  ELSE CAST(mycol AS varchar)
  END AS mycol
FROM mytbl

If this isn't correct, can you give examples of the data and how you want it returned?

--James

 

[icebo3]

actually i understand what the right function for as u trim ur data from right to left with 2 space .... this function has help me as my records are month (1- 12), i need to sort it by the number, but it will be 1,11,12,2,3,4,5... so but adding a '0' to 1-9 it will be 01, ...09,10,11,12 ... then i am able sort ...


my other question if let say it was not month but something else ... so i belive that is where the 'len' function come in...

is there anywhere we can use the "convert" function

 

[collierd]

I have a similar problem but wany to store this data in a table as an int

Basically for sorting purposes

Can that be done as re-casting
i.e. cast(RIGHT('0' + CAST(mycol AS varchar), 2) as int)
just drops the leading zero again

Any thoughts or do I just have to store it has a char

Damian.

 

[JamesLean]

If you want to store leading zeroes (eg for sorting) then you must store it as character data.

--James

 

[Glyndwr]

icebo3

declare @mask int
select @mask =max(len(id))
from sysobjects

select stuff(replicate('0',@mask),@mask-len(id),len(id),id)
from sysobjects


is this what you're looking for?