Filter a column on Oracle SQL developer like '% %'-CodePudding

I want to display all the names in a table that contains the character ' ' but it doesn't work on Oracle SQL developer, while the column contains names with ' '

Select *
from Table where NAME like '% %';

==> I got an empty output

Example:

Could you help me please ?!

CodePudding user response：

You can use this statement:

Select *
from Table where NAME like '%' || chr(43) || '%';

Thank you

CodePudding user response：

In Oracle, the character has a special meaning in the LIKE operator, as it represents a single character that can be any character. This means that the % % pattern in your WHERE clause will match any string that contains a single character between any other characters, but it will not match a character itself.

To match a character in a LIKE clause, you will need to escape it using the ESCAPE clause. The ESCAPE clause specifies a character that will be treated as an escape character, allowing you to match the actual character instead of its special meaning.

Here's an example of how you can use the ESCAPE clause to match the character in a LIKE clause:

SELECT * FROM Table WHERE NAME LIKE '% %' ESCAPE ' ';

This will match any string that contains a character, regardless of its position in the string.