Обсуждение: Underscore "_" in LIKE pattern
Hi there,
I would like to get a list of all tables in my schema which begin with "in_". But as the "underscore (_) in pattern stands for (matches) any single character", my query:
SELECT * FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'in_%' ORDER BY tablename ASC
doesn't exactly do what I want.
It retrieves me tables like this one (which is what I want):
in_water_use_ind
but also tables like this one (which is not what I want):
ind_gdp
I am sure this is just a minor thing. But I have no idea how to solve it.
Thanks for any hints!
Stef
Hi Stef,the underscore has to be escaped:SELECT * FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'in\\_%'ORDER BY tablename ASCExcerpt from Manual:To match a literal underscore or percent sign without matchingother characters, the respective characterin pattern must be preceded by the escape character. The default escape character is the backslashbut a different one can be selected by using the ESCAPE clause. To match the escape characteritself,write two escape characters.Note that the backslash already has a special meaning in string literals, soto write a pattern constantthat contains a backslash you must write two backslashes in an SQL statement (assuming escapestringsyntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslashmeanswriting four backslashes in the statement. You can avoid this by selecting a different escapecharacter withESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to thestring literal parser, so youstill need two of them.)<span style="font-family: Verdana"> Alternative use of a regular expression: SELECT * FROM pg_tables WHERE schemaname='public'AND tablename *~ 'in_' ORDER BY tablename ASCbye...Ludwig <br />