Testing strings for multiple substrings

Question

Testing strings for multiple substrings

david_h_edmonds

Default port

Points: 1430
More actions
October 5, 2018 at 5:53 am

#363759

Hello all,
I am stuck with how to approach this task.
I have an error table that has error text held as a string.
I have a lookup table with generic substrings in it and their corresponding categories.
Example here:
What I want to do is test the error string for all of these substrings and then return the category if it finds a match.
Does anyone have any experience doing this? I can't quite decide where to begin.
Sorry to be so vague.
Dave

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

One method is to use CHARINDEX. Performance on a large dataset will (probably) not be very good, however. Doing lots of pattern matching on a string in SQL Server is not it's forté. Have a look at the documentation and have a try; if you don't succeed post your attempt and we'll see where you went wrong.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, or you can use LIKE. I don't know whether it'll be more or less efficient than CHARINDEX, or exactly the same - you'll need to test.

SELECT l.Category FROM ErrorTable e JOIN Lookup l ON e.ErrorText LIKE '%' + l.SearchString + '%'

John

david_h_edmonds Default port Points: 1430 More actions · Answer 3

John Mitchell-245523 - Friday, October 5, 2018 6:33 AM
Yes, or you can use LIKE. I don't know whether it'll be more or less efficient than CHARINDEX, or exactly the same - you'll need to test.
SELECT l.Category FROM ErrorTable e JOIN Lookup l ON e.ErrorText LIKE '%' + l.SearchString + '%'
John

Hi Thanks for the replies. I used Like and it worked just fine. I only had 25 substrings to test though so performance was never going to suffer in this instance.

Regards

Dave

Joe Torre SSChampion Points: 10248 More actions · Answer 4

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-2017

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Joe Torre - Friday, October 5, 2018 3:05 PM
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-2017

Ok, Joe. Let's say you took the time to setup FTS. What would the query be to solve the OP's problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

david_h_edmonds - Friday, October 5, 2018 5:53 AM
Hello all,
I am stuck with how to approach this task.
I have an error table that has error text held as a string.
I have a lookup table with generic substrings in it and their corresponding categories.
Example here:
What I want to do is test the error string for all of these substrings and then return the category if it finds a match.
Does anyone have any experience doing this? I can't quite decide where to begin.
Sorry to be so vague.
Dave

Have you some real samples of real error strings that we could take a look at?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)