[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Using LIKE operator in MSSQL 2005

Posted on 2009-12-30
Medium Priority
Last Modified: 2012-05-08
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.


Question by:CubicleGuy
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26149521
this will be better

where charindex('021226', Column_value) > 0

Assisted Solution

igni7e earned 100 total points
ID: 26149539
For the harder problem, just use substring and join using equals
LVL 10

Expert Comment

ID: 26151150
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Accepted Solution

lof earned 800 total points
ID: 26151200
Oh, and the first part of the question. Cursors are evil so don't use them unless you have to.

the attached query will do it for you in an dataset oriented manner.
it works with the table from the previous example. all you have to do is specify @searchId.

if you set it to '124' it will return '124-4' which is next available alias
if you will set it to '456' then you will get '456' back

if you are interested in not-using cursors have a look at my article
declare @searchId varchar(16)
set @searchId = '124'

select @searchId 
	  + isnull(
						when CHARINDEX('-', value) > 0 
						then STUFF(value,1,CHARINDEX('-', value),'') 
						else '1' 
				) + 1
from LikeSearch 
where value = @searchId or value like @searchId+'-%'

Open in new window


Author Comment

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

Thanks all.

Back with you ASAP.

Assisted Solution

ibrahimammari earned 100 total points
ID: 26152819
you can create a view like the on i use to capture Duplicate companies

SELECT distinct  t1.comp_companyid, t1.comp_name from company t1 ,company t2 where t1.comp_companyid<>t2.comp_companyid and
( t1.comp_name = substring(t2.comp_name,1,3)
or t1.comp_name = substring(t2.comp_name,1,4)
or t1.comp_name = substring(t2.comp_name,1,5)
or t1.comp_name = substring(t2.comp_name,1,6)
or t1.comp_name = substring(t2.comp_name,1,7)
or t1.comp_name = substring(t2.comp_name,1,8)
or t1.comp_name = substring(t2.comp_name,1,9)
or t1.comp_name = substring(t2.comp_name,1,10)
or t1.comp_name = substring(t2.comp_name,1,11)
or t1.comp_name = substring(t2.comp_name,1,12)
or t1.comp_name = substring(t2.comp_name,1,13)
or t1.comp_name = substring(t2.comp_name,1,14)
or t1.comp_name = substring(t2.comp_name,1,15)
or t1.comp_name = substring(t2.comp_name,1,16)
or t1.comp_name = substring(t2.comp_name,1,17)
or t1.comp_name = substring(t2.comp_name,1,18)
or t1.comp_name = substring(t2.comp_name,1,19)
or t1.comp_name = substring(t2.comp_name,1,20)
or t1.comp_name = substring(t2.comp_name,1,21)
or t1.comp_name = substring(t2.comp_name,1,22)
or t1.comp_name = substring(t2.comp_name,1,23)
or t1.comp_name = substring(t2.comp_name,1,24)
or t1.comp_name = substring(t2.comp_name,1,25)
or t1.comp_name = substring(t2.comp_name,1,26)
or t1.comp_name = substring(t2.comp_name,1,27)
or t1.comp_name = substring(t2.comp_name,1,28)
or t1.comp_name = substring(t2.comp_name,1,29)
or t1.comp_name = substring(t2.comp_name,1,30)
or t1.comp_name = substring(t2.comp_name,1,31)
or t1.comp_name = substring(t2.comp_name,1,32)
or t1.comp_name = substring(t2.comp_name,1,33)
or t1.comp_name = substring(t2.comp_name,1,34)
or t1.comp_name = substring(t2.comp_name,1,35)
or t1.comp_name = substring(t2.comp_name,1,36)
or t1.comp_name = substring(t2.comp_name,1,37)
or t1.comp_name = substring(t2.comp_name,1,38)
or t1.comp_name = substring(t2.comp_name,1,39)
or t1.comp_name = substring(t2.comp_name,1,40)
or t1.comp_name = substring(t2.comp_name,1,41)
or t1.comp_name = substring(t2.comp_name,1,42)
or t1.comp_name = substring(t2.comp_name,1,43)
or t1.comp_name = substring(t2.comp_name,1,46)
or t1.comp_name = substring(t2.comp_name,1,47)
or t1.comp_name = substring(t2.comp_name,1,48)
or t1.comp_name = substring(t2.comp_name,1,49)


Author Closing Comment

ID: 31671426
I'll read the article - these answers all go to the heart of my issue.  Thanks.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question