Using LIKE operator in MSSQL 2005

Posted on 2009-12-30
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
    this will be better

    where charindex('021226', Column_value) > 0
    LVL 4

    Assisted Solution

    For the harder problem, just use substring and join using equals
    LVL 10

    Expert Comment

    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

    LVL 10

    Accepted Solution

    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

    LVL 1

    Author Comment

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

    Thanks all.

    Back with you ASAP.

    Assisted Solution

    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)

    LVL 1

    Author Closing Comment

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

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now