how can i get the number with prefixing zeors in a select query

• natarajan_s

  Grasshopper

  Points: 19

  More actions

  March 16, 2004 at 5:55 am

  #161709

  i need the following requiremnts

  i have a numeric and date colimn in sql. i need the following result

  example col1 number 123456.99

  i need it in a query result as

  00000012345699

  and col2 in date format

  09/09/2001

  i need the query result as follows

  09092001

  29102000

  like this

  thanks

  Regards

  natarajan

   

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 16, 2004 at 6:10 am

  #498929

  SELECT REPLACE(REPLACE(STR(col1,15,2),'.',''),' ','0')

  SELECT REPLACE(CONVERT(varchar,col2,103),'/','')

  Far away is close at hand in the images of elsewhere.
  Anon.

• Bert De Haes

  Hall of Fame

  Points: 3107

  More actions

  March 17, 2004 at 12:08 am

  #499074

  for the number column col1 : ( result has length 14 )

  select col1 = case when col1 < 0 then '-' else '' end + right(replicate('0',14) + convert(varchar(255),abs(col1)),14)

  for the date column :

  select col2 = convert(char(8),col2,112)