Skip site navigation (1) Skip section navigation (2)

Re: "_" in a serach pattern

From: Peter Koczan <pjkoczan(at)gmail(dot)com>
To: Jessica Richard <rjessil(at)yahoo(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: "_" in a serach pattern
Date: 2007-07-22 05:54:04
Message-ID: 46A2F0FC.4070501@gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi, Jessica,
> 1. How do I get rid of the nonstandard warning, but still using the 
> index search?
You have two options.

- Turn off the warnings in the postgresql.conf file. Use this with 
caution (or don't use it at all) as it does pose a potential threat for 
SQL injections if other options aren't properly set. Read up at 
http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html 
for more detail.
- Use escape-string formatting. This is the best practice since it is 
standards-conforming and more secure. You can do it, for instance, as
    select name from table where name like 'A!_B%' escape '!';
You can escape with most characters, and it's mostly a matter of 
personal preference.
>
> 2. How do I search with a wild card % in the middle of the pattern? 
> Would varchar(80) or char(80) make a difference about the wild card 
> search (% in the middle)?
I think that postgres is seeing the trailing whitespace on the end of 
the char type and not accounting for it in the search. In this case, 
varchar or text types would make a difference since they don't do 
whitespace padding (unless you force it in). You should remember that if 
you plan on converting the data type, trim the extraneous whitespace 
first. However, I would switch to varchar/text so it saves some space 
and saves you these headaches, unless there's an absolute need for 
fixed-length char fields. I use text almost exclusively for string data 
since it's arbitrary-length, I almost never have to worry about overflow 
or later administration.

If you don't want to convert data, you can use the rtrim() function 
(i.e. "select rtrim(name) from ...").

Peter

> ------------------------------------------------------------------------
> Need a vacation? Get great deals to amazing places 
> <http://us.rd.yahoo.com/evt=48256/*http://travel.yahoo.com/;_ylc=X3oDMTFhN2hucjlpBF9TAzk3NDA3NTg5BHBvcwM1BHNlYwNncm91cHMEc2xrA2VtYWlsLW5jbQ-->on 
> Yahoo! Travel. 


In response to

Responses

pgsql-admin by date

Next:From: Thiago MalufDate: 2007-07-23 13:59:17
Subject: Disable access shell command in psql
Previous:From: Michael FuhrDate: 2007-07-22 04:41:34
Subject: Re: "_" in a serach pattern

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group