Why does using an Underscore character in a LIKE filter give me all the results?

by

Modify your WHERE condition like this:

WHERE mycolumn LIKE '%\_%' ESCAPE '\'

This is one of the ways in which Oracle supports escape characters. Here you define the escape character with the escape keyword. For details see this link on Oracle Docs.

The '_' and '%' are wildcards in a LIKE operated statement in SQL.

The _ character looks for a presence of (any) one single character. If you search by columnName LIKE '_abc', it will give you result with rows having 'aabc', 'xabc', '1abc', '#abc' but NOT 'abc', 'abcc', 'xabcd' and so on.

The '%' character is used for matching 0 or more number of characters. That means, if you search by columnName LIKE '%abc', it will give you result with having 'abc', 'aabc', 'xyzabc' and so on, but no 'xyzabcd', 'xabcdd' and any other string that does not end with 'abc'.

In your case you have searched by '%_%'. This will give all the rows with that column having one or more characters, that means any characters, as its value. This is why you are getting all the rows even though there is no _ in your column values.

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. sql query to return differences between two tables
  3. Generate SQL Create Scripts for existing tables with Query
  4. SQL Server 2005 How Create a Unique Constraint?
  5. SQL Server: Make all UPPER case to Proper Case/Title Case
  6. How do I delete from multiple tables using INNER JOIN in SQL server
  7. Does Foreign Key improve query performance?
  8. SQL : BETWEEN vs =
  9. What is a ‘multi-part identifier’ and why can’t it be bound?
  10. SQL Server convert string to datetime
  11. sql server select first row from a group
  12. How do I make a composite key with SQL Server Management Studio?
  13. CASE WHEN statement for ORDER BY clause
  14. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  15. Return number of rows affected by UPDATE statements
  16. CTE error: “Types don’t match between the anchor and the recursive part”
  17. Automate Version number Retrieval from .Dtsx files
  18. Execute Dynamic Query with go in sql
  19. TransactSQL to run another TransactSQL script
  20. How can I get a list of element names from an XML value in SQL Server
  21. SQL server identity column values start at 0 instead of 1
  22. The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  23. How to correctly insert newline in nvarchar [duplicate]
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  27. How to subtract 30 days from the current date using SQL Server
  28. Add a row number to result set of a SQL query
  29. Use one CTE many times
  30. How to do a case sensitive GROUP BY?

Leave a Comment