Search code examples
sqlsql-serversql-like

SQL Server storing wildcards in data

I was shown a table in SQL Server that included % wildcards in the actual table data. ie:

Column1 : Column2 : Column3
1        %         2
3        %         %

This allowed the developer to run like queries on this data and the underlying data would also evaluate as a secondary pattern match.

SELECT * FROM tbl WHERE Column1 LIKE '123' would return no records.

SELECT * FROM tbl WHERE Column2 LIKE '123' would return all records.

SELECT * FROM tbl WHERE Column3 LIKE '2' would return all records.

SELECT * FROM tble WHERE Column3 LIKE '123' would return only the last record.

I have looked everywhere to find out how this is possible, but I can only find a bunch of information on like queries where the wildcard is in the query itself, not in the data.

  1. What is this voodoo called and where can I find documentation?
  2. Do other databases support it?
  3. Does this feature exist in an search indexes (SOLR or ElasticSearch) without writing extra code?

UPDATE So, it was not so much voodoo as my poor memory. Inverting the the like makes much more sense.
SELECT * FROM tble WHERE '123' LIKE Column1

Solution

  • You've got your examples inverted.

    You need to use

    SELECT * FROM tbl WHERE '123' LIKE Column1

    etc.

    To give your desired results (demo)

    There is no particular "voodoo". LIKE takes a string with a match expression on the left hand side and a string with a pattern on the right hand side and doesn't care if either or both strings come from a column or not. So the documentation you need is just the docs for LIKE.

    It will behave this way in all DBMSs I imagine.

    But whilst this developer may have found it convenient all such queries will result in a table scan rather than being able to locate the desired rows potentially more efficiently with seeks.