I want to find string that have % character
For example, when one row's column has %%%%%,
SELECT * FROM table WHERE column LIKE '%value%';
If value is %, it returns all.
How can I search only that row?
-
This link might be helpful.. Like queryFaNtAMode– FaNtAMode2015-11-13 05:57:33 +00:00Commented Nov 13, 2015 at 5:57
-
3Possible duplicate of How to escape literal percent sign when NO_BACKSLASH_ESCAPES option is enabled?Keale– Keale2015-11-13 06:09:28 +00:00Commented Nov 13, 2015 at 6:09
Add a comment
|
4 Answers
Or you could use any escape character with:
LIKE '%|%%' escape '|'
Comments
You should escape it with a backslash...
\%
Comments
You can use something like this to escape all % characters in PHP:
$original_value = "%%%%%";
$temp = str_split($original_value, 1);
$escape_value = '\\'.implode('\\', $temp);
echo $escape_value;
Result (escaped value):
\%\%\%\%\%
Comments
Let’s say you want to find any fields that contain the text “100%”, so you put together this query:
Square Bracket Escape:
You can surround the % or _ with square brackets to tell SQL Server that the character inside is a regular character.
SELECT * FROM tablename WHERE fieldname LIKE ‘%100[%]%’
T-SQL ESCAPE Syntax:
Alternatively, you can append the ESCAPE operator onto your query, and add a \ character before the value you want to escape.
SELECT * FROM tablename WHERE fieldname LIKE ‘%100\%%’ ESCAPE ‘\’
The ESCAPE ‘\’ part of the query tells the SQL engine to interpret the character after the \ as a literal character instead of as a wildcard.
The easiest way is to include the characters I don't want, for example square brackets and slash:
SELECT * FROM Table
WHERE Name LIKE '%[\]\[/]%' ESCAPE '\'
