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”.

  • How do I get multiple rows when value > 1
  • INSERT XML into SQL Server 2008 database
  • How to get expression from string
  • SQL Delete clears the table instead of erroring
  • How to solve msdb dbo sp send dbmail File attachement in SQL windows server
  • SQL Server: Database stuck in “Restoring” state
  • 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!

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.