Underscore "_" in LIKE pattern

Started by Stefan Schwarzeralmost 18 years ago2 messagesgeneral

stefan.schwarzer@grid.unep.ch

almost 18 years ago

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

Ludwig Kniprath

ludwig@kni-online.de

almost 18 years ago

In reply to: Stefan Schwarzer (#1)

Re: Underscore "_" in LIKE pattern

<div>Hi Stef, the underscore has to be escaped: SELECT * FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'in\\_%' ORDER BY tablename ASC Excerpt from Manual: To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default
escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters. Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to the string 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 ASC bye... Ludwig </div><div> </div> <div> </div> <div> </div> <div> </div> <div> </div>

Import Notes

Resolved by subject fallback