want to pull data with leading 0

[rush81]

Hello All,

I want to pull data against two fields from table. The combination of these fields give unique result like '123 VA' or '0123 VA' One field has entries like 0123 and 123, one entry with leading 0. They are same but this is data entry mistake. Can you suggest me on how to go about this one?

I could find exact matches but need to work on this substring match. Please respond at the earliest. Also do let me know if any other information is needed.

Thanks in advance.

[ck9663]

first, check if it's really unique...

select field1 + field2, count(*) from yourtable
group by field1 + field2 having count(*) > 1

if there are records returned, then the combination of these fields are not unique. and you might want to fix that first....

if everything is fine...these are records that have leading zero but have a corresponding record in your table with no leading zero....

select * from yourtable
where substring(field 1 + field2,1,1) = '0'
and substring(field 1+field2,2, length(field1 + field2)-1) in (select field1 + field2 from yourtable)

-- ck

[rush81]

Thanks CK..that helped!!

first, check if it's really unique...

select field1 + field2, count(*) from yourtable
group by field1 + field2 having count(*) > 1

if there are records returned, then the combination of these fields are not unique. and you might want to fix that first....

if everything is fine...these are records that have leading zero but have a corresponding record in your table with no leading zero....

select * from yourtable
where substring(field 1 + field2,1,1) = '0'
and substring(field 1+field2,2, length(field1 + field2)-1) in (select field1 + field2 from yourtable)

-- ck