SQL Server: LIKE clause syntax for reserved characters like percent % #sql #sqlserver

The most common thing I hit when trying to do a LIKE is trying to use the percent sign (%).
Fortunately the LIKE clause has a means of escaping the reserved characters, you simply stick a different letter in front of the reserved character then indicate that is the escape character, best to see this in an example:

SELECT * FROM
(
	SELECT 'me' AS val
	UNION ALL
	SELECT 'you' AS val
	UNION ALL
	SELECT 'me%20and%20you' AS val
) vals
WHERE val LIKE '%/%%' ESCAPE '/'

Here I am escaping the percent sign with a forward slash and the result would be ‘me%20and%20you’.
If I didn’t then no matter how many percent signs I used there is no combination to actually filter only those rows that contain a percent sign.

Advertisements

Leave a Reply

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