SQL Selection to do a partial match - middle

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) )
hojohappyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You can use the single character wildcard "_":
WHERE SAPMATUPC.EAN11 LIKE "___" & STEPUPC.ItemID & "_"
0
dsackerContract ERP Admin/ConsultantCommented:
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)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PadawanDBAOperational DBACommented:
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>;

Open in new window


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>;

Open in new window


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
Kyle AbrahamsSenior .Net DeveloperCommented:
you can use this:

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

will pad left the 0's.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.