CODEWITHSUNDEEP
sqlsql-servert-sql
Alex
Alex

Reputation: 35

Find alpha-numeric value in a field with several alpha-numeric values in SQL

I have a list of alpha-numeric values and I need to search my SQL Server database for entries that contain these "words".

Unfortunately the data in the DB is of very bad quality. The varchar field that might have these values can contain (a single or) several alpha-numeric values that are seperated by " " "," or "-". The length of the values varies.

Let's say I'm looking for database records with the value "X1337" and my DB entries look like this:

99802 99803 X1337
99802 X9803 102379 70X1337
99842 99893 , X1337 12608, X25663 125498
42X13379
X1337, 99802 99803
X1337

My goal is to find the following 4 entries:

99802 99803 X1337
99842 99893 , X1337 12608, X25663 125498
X1337, 99802 99803
X1337

I must NOT find the following 2 entries:

99802 X9803 102379 701337
42X13379

Is this task achievable using SQL?

My thought was to apply a pattern to something like this but I'm not really good with patterns:

DECLARE @numberstolookfor TABLE (Number varchar(60))
INSERT INTO @numberstolookfor VALUES ('1027903')
INSERT INTO @numberstolookfor VALUES ('X1337')

SELECT *
FROM [myDB]
Where DBFIELDINFO like (SELECT Number FROM @numberstolookfor)

Any help is appreciated!

Upvotes: 1

Views: 244

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

We need to be efficient LIKE users here, and we can try to cover all possible use cases where X1337 might appear:

SELECT *
FROM yourTable
WHERE
    ' ' + DBFIELDINFO LIKE '% X1337 %' OR
    ' ' + DBFIELDINFO LIKE '% X1337,%' OR
    ' ' + DBFIELDINFO LIKE '% X1337';

screen capture from demo link below

Demo

We first add a single space to the start of the field to be checked. After doing this, we only need to check for [ ]X1337 followed by space or comma (for occurrences at the start or middle), or [ ]X1337 followed by nothing, for occurrences at the very end of the field.

Upvotes: 1

Related Questions