asked on

Using LIKE operator in MSSQL 2005

We are loading new records to a database and we know that there are duplicate ClientIDs [varchar(10)] in the records - some done by human error, others due to large paper files. We're OK with this, but we need a way to append a suffix to the duplicates so they can still be searched.

I have two challenges.
1. Appending the suffix (-2,-3,etc.) I think I can do this with a CURSOR. The first record will never have a suffix, only the duplicates. The suffix sequence will always start with -2 so there is at least one "original ID" record left.

2. The stickier problem is finding the records that have previously been run through this sequence and now have a suffix -- which is why the problem using LIKE emerged.

I can find **exact** duplicates between our working import table and our core table; '021226' will always join to '021226'. But I need to find '021226' LIKE (%column_value%) so I'll find '021226-2' and '021226-3'.

I'm having trouble using the value from a column in a LIKE operation. All help greatly appreciated.

Thanks

CVM

Aneesh

this will be better

where charindex('021226', Column_value) > 0

SOLUTION

igni7e

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lof

a quick working sample is attached.

you don't need complicated string operations you were offered.
like will do just fine

create table LikeSearch (value  varchar(16))

insert into LikeSearch values ('123')
insert into LikeSearch values ('124')
insert into LikeSearch values ('124-2')
insert into LikeSearch values ('125')
insert into LikeSearch values ('124-3')

declare @searchId varchar(16)
set @searchId = '124'

select * 
from LikeSearch 
where value = @searchId or value like @searchId+'-%'

Open in new window

ASKER CERTIFIED SOLUTION

lof

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CubicleGuy

ASKER

OK - I'm going to study all of this carefully.

Thanks all.

Back with you ASAP.

SOLUTION

ibrahimammari