• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

"Backwards" TSQL string search

I need to do a kind of "backwards" substring search in a SQL 2008R2 database.  The column I need to search contains a subset of a tracking number, with a minimum of 10 characters.  When a package arrives, it is scanned, but the result is a value which may have many more characters.   What I need to know is whether that scanned number contains any of the strings in the table column of interest.

So, the user enters '1234567890' in the table column.  A package comes in and the tracking number is 'ABCD1234567890WXZ'.   I want that to be a "hit".

It's easy to do it the other way (i.e. if the full tracking # was in the table and I was searching for a substring in my query), but my brain is coming up empty on figuring out a good way to do this.  The table has a couple hundred thousand rows and I don't think my client will do a full text index.  The solution needs to be reasonably efficient and fast.

Can anyone unclog my brain?   This should be easy!


1 Solution
George HirstCommented:
select * from table where tracking_number_field like '%1234567890%'
searching on the middle of a string isn't going to be efficient but the syntax is easy

select * from your_table where 'ABCD1234567890WXZ' like '%' + your_column + '%'


select * from your_table where charindex(your_column, 'ABCD1234567890WXZ') > 0
Cornelia YoderArtistCommented:
SELECT * FROM Table WHERE  INSTR(Column,'1234567890') >0
BobSacksAuthor Commented:
Thanks to those who responded.  I guess my question wasn't well stated.  Couple of you suggested solutions which assumed that I HAD the target substring to submit to the query.  I don't.   I have only what gets scanned at the time the package is received.

SDSTUBER - I tried what you suggested.  It works.
select * from your_table where 'ABCD1234567890WXZ' like '%' + your_column + '%'

There are two problems.  As you suggested it is really slow and it hits on a blank column.  Adding a filter for  your_column  <> ''  and changing the SELECT to SELECT TOP 1 seems to address both issues.

You've given me a place to start.  Thanks!
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

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now