CODEWITHSUNDEEP
sqlsql-servert-sql
David Brierton
David Brierton

Reputation: 7397

Removing leading 0's in SQL Server query

How do I trim the leading 0's from assoc_userid?

SELECT assoc_name
FROM table
WHERE assoc_userid= #arguments.op_id#

This column used to be assoc_id and would have values like 1,2,3,4,5,..,091,101

Now the values for assoc_userid are 001,002,003,004,005,091,101

I tried WHERE Replace(Ltrim(Replace('assoc_userid', '0', ' ')), ' ', '0') = #arguments.op_id#

Upvotes: 1

Views: 129

Answers (2)

beyt3i
beyt3i

Reputation: 31

Maybe you can use patindex to find index of non-zero character and use substring to get the part of you want. Here is the sample:

declare @input varchar(max)
select @input = '0000100'

select SUBSTRING(@input,PATINDEX('%[^0]%',@input),LEN(@input))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You could convert them to numbers and compare:

WHERE TRY_CONVERT(INT, assoc_userid) = TRY_CONVERT(INT, #arguments.op_id#)

Upvotes: 4

Related Questions