ltrim in sql server

I hav开发者_Go百科e 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

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
)

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.