Re: [GENERAL] Underscore "_" in LIKE pattern

[ludwig]

  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 matching other 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, so to write a pattern constantthat contains a backslash you must write two backslashes in an SQL statement (assuming escape stringsyntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslashmeans writing four backslashes in the statement. You can avoid this by selecting a different escapecharacter with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to thestring literal parser, so you still need two of them.) Alternative use of a regular _expression_:  SELECT * FROM pg_tables WHERE schemaname='public' AND tablename *~ 'in_' ORDER BY tablename ASCbye...Ludwig 

[GENERAL] Underscore "_" in LIKE pattern

[Stefan Schwarzer]

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