SQL query to find record from superset of characters
I have an application that will provide me with a string from which some unknown portion of that string may be the entire string found in the column of another record, i.e.,
I am provided with “A12345B” and there may be a record in the table whose [SerialNumber] column is equal to “12345” or perhaps to “123”.
Is there a sql query or algorithm you can suggest I use to find the matching records for such a situation? Thanks!
3 Solutions collect form web for “SQL query to find record from superset of characters”
Here’s a couple of ways to achieve that:
Assuming we have a search variable:
DECLARE @Input NVARCHAR(50) = 'ABC12345'
Using LIKE Operator
SELECT * FROM MyTable WHERE @Input LIKE '%' + SerialNumber + '%'
If you need to exclude blank
SerialNumbers, then add this line:
AND SerialNumber <> ''
Using CHARINDEX Function
SELECT * FROM MyTable WHERE CHARINDEX(SerialNumber, @Input) > 0
If I understand your question correctly you’re looking for
CHARINDEX. It accepts two strings and looks for one in the other.
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
So what you’re saying is you are given a long character string and you need to find any value that is a substring of that original character string that is fed to your query. If that is the case, then the following will work:
DECLARE @StringYouAreGiven VARCHAR(255) = 'A12345B' SELECT * FROM [Your Table Name Here] WHERE PATINDEX(CAST(SerialNumber AS VARCHAR(255)), @StringYouAreGiven) > 0 --If SerialNumber is a string datatype then the overhead of a CAST is not necessary)
This will take the value in the SerialNumber column and compare it to the value passed in “in this case ‘A12345B’). If the value in the SerialNumber column appears anywhere in the value passed in, the PATINDEX (pattern index) will be greater than 0 and the record will be returned. Hope this helps!