"Backwards" TSQL string search
Posted on 2014-04-03
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!