Number2 Blog - myITforum

THE PROBLEM

You've written a SQL query or a WQL query (like an SMS/CM collection query) and you need to look for a string that has a percent % or underscore _ in it.  The problem is, those characters are special wildcard characters to SQL/WQL.  So how do you "escape" those characters and write a query to look for all records that have a percent sign or an underscore in them?

THE SOLUTIONS

SOLUTION 1 (for SQL and WQL)

Use square brackets [] to signify you're looking for that specific character.

SELECT
   *
FROM
   v_MyView
WHERE
   Completion LIKE '100[%]'

This will find all records from the v_MyView view with 100% in the completion field.

MORE INFO

Let's quickly talk about those special wildcards...a wildcard character you're probably all familiar with is the % sign.  It basically means match 0 or more of any characters (equivalent to * in shell/batch scripting). 

Then there's the underscore _ which means match any single character. 

But there's also these brackets [ ] which let you specify a range (where [0-9] means a single character matching the number 0 through 9 or [a-g] means a single character matching any letter from a through g) or a set (where [135] means a single character matching a 1, 3 or 5 and [adf] means any single character matching a, d or f). 

You can also put those brackets next to each other to specify multiple ranges (like [0-9][0-9] representing every number between 00 and 99].  When you put a percent % inside of these brackets, you're specifying a "set" of single characters consisting of just the % character, likewise with the underscore.

SOLUTION 2 (for SQL only)

Use the ESCAPE clause!

Here's an example of using the backslash as an escape character:

SELECT
   *
FROM
   v_MyView
WHERE
   Completion LIKE '100\%' ESCAPE '\'

This will also find all records from the v_MyView view with 100% in the completion field.  Here we're explicitly telling SQL what escape character to use and the immediately following character will be treated like a part of the string and not like any special wildcard that it may be.

--- here's an example of using the exclamation point as an escape character

SELECT
   *
FROM
   v_MyView
WHERE
   Completion LIKE '100!%' ESCAPE '!'

That should be sufficient.  I hope this is helpful!

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog