Zero Supression

• PFlorenzano

  SSCrazy

  Points: 2120

  More actions

  February 4, 2004 at 8:31 am

  #161609

  Hello everyone,

  I did some extensive research on this subject and came up with nothing.  Is there a way in SQL Server to zero supress numbers?

  For example, the output should look like this 3942 instead of 000000003942.

  Any ideas?

  Thank you,

  Pete

  Peter M. Florenzano
  
  Database Administrator
  

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 4, 2004 at 8:52 am

  #492764

  If you always have only leading 0s you can use something like

  select cast('000000003942' as int)

             

  -----------

  3942

  (1 row(s) affected)

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• guarddata

  SSCertifiable

  Points: 6583

  More actions

  February 4, 2004 at 10:07 am

  #492774

  If there is more to the value so you cannot depend on it being INT, BIGINT, etc.  You could  do something like

  select REPLACE( LTRIM( RTRIM( REPLACE( ErrMessage, '0', ' ' ) ) ), ' ', '0' )

  Guarddata-