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