#0356 – SQL Server – Search with the LIKE operator and the underscore (_) wildcard character


String searches in most applications are based on the “contains” or “begins-with” paradigms – which are implemented by the use of the LIKE logical operator. The LIKE operator allows for the use of wildcard characters (underscore being one of them), which is the part that most people unknowingly overlook when they have been working with T-SQL for quite some time.

One such incident happened the other day at work when I was asked by a colleague to help him out with a query that appeared correct, but failed to give the expected results. For the sake of brevity, I have condensed the issue into a test scenario below:

DECLARE @testTbl TABLE (Value VARCHAR(50));

INSERT INTO @testTbl (Value)
VALUES ('SQL 2012'),
('SQL_2014');

SELECT * FROM @testTbl WHERE Value LIKE 'SQL_20%';

image

As can be seen in the screenshot above, the results are not what one would expect them to be at the first glance. One would have expected it to return only the string “SQL_2014” and yet the T-SQL query is returning both the records.

Normally, data coming in via flat-files from raw-data collection systems would require such kinds of string searches and manipulations. A well designed system would have these static values as a look-up so that all references look at the same version of the string value

Solution(s)

There are not one, but two possible solutions to choose from to work around this issue.

Using square brackets ([]) around the wildcard character

Because we want to consider the underscore as a character and not as a wildcard, the easiest thing to do would be to surround it with square brackets, similar to what is shown in the query below.

SELECT * FROM @testTbl WHERE Value LIKE 'SQL[_]20%';

image

Using the ESCAPE keyword

The other option, which I have discussed in one of my earlier posts, is to use the ESCAPE keyword. The ESCAPE keyword works on the basis of a user specified escape sequence. When encountered, the query engine will simply ignore the wildcard character and treat it as a normal character.

SELECT * FROM @testTbl WHERE Value LIKE 'SQL*_20%' ESCAPE '*';

image

Conclusion

The moral here is that one must always remember the basics – this thumb rule not only applies to software, but in everything that we go about doing in our day to day lives.

The other key learning that I encourage everyone to take from this is that Occam’s Razor is real – from the multiple explanations that a problem may have, the explanation with the least number of assumptions should be selected as the answer. In this case, the simplest theory was –  human error, which was indeed the case.

References

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s