Table linking problems

• Dave Foster

  SSC Veteran

  Points: 215

  More actions

  March 28, 2006 at 7:50 am

  #114042

  I would like to link a column, 'Account number' between two different databases.  The problem I have is that in one database the 'Account number' field is numeric and therefore strips all leading zero's.  The second database has the same column, but it is set up as CHAR which allows leading zeros.  The number of leading zeros in the table varies from account to account.  I would like to truncate the leading zeros in the CHAR table, or find some other way to make the linking statement ignore them.  Any suggestions?

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 28, 2006 at 9:48 am

  #629240

  Is there some reason why you can't just use cast? See example below...

  --data

  declare @t table ([Account number] char(10))

  insert @t

            select '00004'

  union all select '0010'

  union all select '1'

  union all select '0000200'

  union all select '00002'

  --calculation

  select [Account number], cast([Account number] as int) from @t

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• Dave Foster

  SSC Veteran

  Points: 215

  More actions

  March 28, 2006 at 12:19 pm

  #629270

  DOH!

   

  That is perfect.  I didn't remember that I could do that. 

   

  Thanks