On 09/07/2010 02:04 PM, Christine Penner wrote:
I have a character field in a table that contains either a file name
or a full path and file name. I need to pick out the ones that have no
full path. I do this by looking for no \. This is what I am doing:
select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\%'
-this gives me all records no matter what has a \ or not
select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\%'
-this gives me nothing again no matter what has a \ or not
I even tried this
select MM_PATH_FILE from MULTI_MEDIA Where position('\' in
MM_PATH_FILE)=0
-this gives me an error
Any other suggestions?
Actually, to expand on my prior answer, there are many ways of doing
this. For instance, you can turn off the escape mechanism:
...like E'%\\%' escape ''
Your basic problem is that by default the \ is being used as an escape
character in your string literal so the %\% is becoming %% before being
used in the "like" clause while %\\% is becoming %\% which, when used in
the like, is the equivalent of searching for a literal percent-sign. The
E'%\\\\%' literal becomes %\\% which is interpreted as a single \ in the
like pattern match.
See http://www.postgresql.org/docs/8.4/static/functions-matching.html
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
