Removing leading zeroes from a Select Distinct

Transact-SQL
1

I'm running a select distinct query to pull only unique data tied to a client. For some of the data, I'm pulling, it is stored with leading zeroes, and for others it isn't. Is there a way to build in functionality to remove the leading zeroes when running the select distinct? I'm currently seeing what are essentially duplicates because the leading zeroes aren't causing the distinct to do it's job correctly.

My query is:
SELECT DISTINCT society, work_no
FROM data1
WHERE songcode = '5645'

and my results are showing:
Society Work_No
A 0123
A 123
B 567

2

Are all of the other characters in the column numeric? If so - then you can cast/convert the value to an integer. If not, then I would recommend adding the leading zeroes to those items that don't have it - that way you have a consistent work_no.

3 Likes
3

They are all numeric so that definitely makes sense. Thanks for your help!

4

First - determine the max length you want to return, for example let's say we want a value that is 10 characters long:

, work_no = right(concat('0000000000', work_no), 10)