PostgreSQL Bugs - BUG #2125: SELECT problem with strings containing \

Author

BUG #2125: SELECT problem with strings containing \

Tony

2005-12-27, 9:24 am

The following bug has been logged online:

Bug reference: 2125
Logged by: Tony
Email address: tonya@ananzi.co.za
PostgreSQL version: 80100
Operating system: XP SP2
Description: SELECT problem with strings containing \
Details:

Hi,

Please refer to the DDL and data below:

When running a query such as:

select folder from public.folders
where lower(folder) like '%c:\\1%'

no rows are returned

However,

select folder from public.folders
where lower(folder) like '%c:%'

does work OK.

When the query string contains a \\ (escaped \) no results are returned.
Other combinations not containing this character work OK. I've tried without
the % wildcard without success so this does not appear to be the problem.
I've also tried it without the lower function either, still no luck. Using
the \\ to insert records works fine (as shown below).

Table DDL:
----------
create table public.folders(
folder varchar(255) ,
lastfolder varchar(255) ) with oids

Here are some INSERTS to populate data:
---------------------------------------
insert into public.folders values ('C:\\1\\', '1');
insert into public.folders values ('C:\\111\\', '111');
insert into public.folders values ('C:\\115x\\', '115x');
insert into public.folders values ('C:\\11tx\\', '11tx');
insert into public.folders values ('C:\\2005\\', '2005');
insert into public.folders values ('C:\\2005\\(12)Dece
mber\\',
'(12)December');
insert into public.folders values ('C:\\2005\\(12)Dece
mber\\051209\\',
'051209');
insert into public.folders values ('C:\\2005\\12 December\\', '12
December');
insert into public.folders values ('C:\\2005\\12 December\\12\\', '12');
insert into public.folders values ('C:\\776\\', '776');

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane

2005-12-27, 11:23 am

"Tony" <tonya@ananzi.co.za> writes:
> When running a query such as:
> select folder from public.folders
> where lower(folder) like '%c:\\1%'
> no rows are returned

This is not a bug --- you've forgotten that backslash is an escape
character in LIKE patterns. You can either double it again:

like '%c:\\\\1%'

or select a different escape character, or maybe better not have any
escape character at all:

like '%c:\\1%' escape ''

See
http://www.postgresql.org/docs/8.1/...#FUNCTIONS-LIKE

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links