1

I have this data in my tables Table1: ProfileID: 0014, 0012, 001 Table2: PurchasedprofileID: 14, 12, 1

select * from Table1 join Table2
on Table1.profileID = Table2.PurchasedprofileID

Should return : 14, 12, 1

How do I use the LTRIM or REPLACE to trim the leading zeros

ProfileId, PurchasedprofileID is of datatype varchar

Thanks Sun

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sun
  • 27
  • 1
  • 8
  • possible duplicate of [Removing leading zeroes from a field in a SQL statement](http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement) – Joe Stefanelli Jul 06 '11 at 21:00

2 Answers2

1

For profileID use CAST ( profileID AS Integer)

Also CAST should be used in JOIN

select * from Table1 join Table2
on CAST(Table1.profileID AS Integer)= CAST(Table2.PurchasedprofileID AS Integer)

In case that non number exist's

SUBSTRING( -- get the substing
profileID
,PATINDEX ( '%[^0]%' , profileID ) -- find the first non zero char
,LEN(profileId)+1-PATINDEX ( '%[^0]%' , expression ) -- calculate the rest string length from the first non zero character
)
niktrs
  • 9,858
  • 1
  • 30
  • 30
1

If you don't need the result as a character type then I think it would be best to Cast the result as an Integer.

Hal Hawkins
  • 11
  • 1
  • Deleted my post with same answer.. Yes. The leading zeros should automatically fall off the numbers if converted to a numeric data type. – Frank Pearson Jul 06 '11 at 21:08