SQL statement with LIKE

Question

I would like to select all records that have an underscore character in their 11th character, so i try this:

SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '%%%%%%%%%%_%%%'

but this doesnt work as expected, can someone help?

Answer 1

Just use the "SUBSTRING" function :

SELECT * FROM "BOM_SUB_LEVEL" where SUBSTRING(TOP_CODE, 11, 1) = "_"

Marc

Answer 2

For a single character wildcard use _. For multiple characters wildcards, use %. To escape a "real" appearance of _, use \_ (thanks Bill!).

Try the following code:

SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '___________\_%'

To further elaborate following Dav's comment, note that '%%%' is exactly the same as '%', since by definition '%' covers multiple characters.