Solved

# SQL Selection to do a partial match - middle

Posted on 2013-10-17
Medium Priority
267 Views
I need a SQL Select statement that will be a partial match on another column.  I need to match the middle part of another column.  For example:

ItemID= 10 digits long     1234567890
EAN11 = 14 digits long.   00012345678904

I need to match ItemID "1234567890" to EAN11 column starting at the 4th position.

SELECT *
FROM STEPUPC
WHERE NOT EXISTS
(SELECT SAPMATUPC.EAN11
FROM SAPMATUPC WHERE STEPUPC.ItemID = LEFT(SAPMATUPC.EAN11 ,10) )
0
Question by:hojohappy

LVL 27

Expert Comment

ID: 39580569
You can use the single character wildcard "_":
WHERE SAPMATUPC.EAN11 LIKE "___" & STEPUPC.ItemID & "_"
0

LVL 20

Accepted Solution

dsacker earned 2000 total points
ID: 39580573
I assume your WHERE NOT EXISTS is actually a NOT match query.

Seems you recently asked for matching the first 10 digits. If the number is going to be embedded anywhere, perhaps a catch-all is what you really want:

``````SELECT *
FROM STEPUPC
WHERE NOT EXISTS
(SELECT SAPMATUPC.EAN11
FROM SAPMATUPC
WHERE CHARINDEX(STEPUPC.ItemID, SAPMATUPC.EAN11) > 0)
``````
0

LVL 10

Expert Comment

ID: 39580603
A couple ways I can think of...

This one may take longer, but will match where the ItemID column contents are anywhere in EAN11 column contents

``````select
*
from
STEPUPC as SU
cross apply( select EAN11 from SAPMATUPC as SM where SM.EAN11 like ( '%' + SU.ItemID + '%' ) ) as SMU
where
SU.ItemID = <whatever item you're looking to match>;
``````

Are the leading characters going to always be zeroes?  If so, you could do this:

``````select
*
from
STEPUPC as SU
cross apply( select EAN11 from SAPMATUPC as SM where SU.ItemID = ltrim( SM.EAN11 ) ) as SMU
where
SU.ItemID = <whatever item you're looking to match>;
``````

Edit:

Also, if you change the left to a right in your posted query, that should work for a fixed length too...  And changed the where clause to explicitly reference to the STEPUPC table.
0

LVL 41

Expert Comment

ID: 39580762
you can use this:

REPLACE(STR(ItemID, 14), SPACE(1), '0')

will pad left the 0's.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll

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

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