Обсуждение: Find \ in text

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?

Christine Penner
Ingenious Software
250-352-9495
christine@ingenioussoftware.com

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?
>
> Christine Penner
> Ingenious Software
> 250-352-9495
> christine@ingenioussoftware.com
>

select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\\\%'

 From here:
http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-LIKE

"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 backslash is still special to the string literal parser, so you
still need two of them to match a backslash.) "

--
Adrian Klaver
adrian.klaver@gmail.com

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

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?
... like E'%\\\\%'

Cheers,
Steve

  On 09/07/10 2:44 PM, Steve Crawford wrote:
>> Any other suggestions?
>
> ... like E'%\\\\%'
>

and, for extra fun, if that SQL statement is a constant string in a C or
similar programming language, you may well need to double up those \'s
again so that SQL sees them as the C/C++/etc parser itself does \ escaping.

     sql_command = "select MM_PATH_FILE from MULTI_MEDIA Where
MM_PATH_FILE NOT ILIKE E'%\\\\\\\\%';"



Seriously, MS Windows programmers really really should use / for
paths... the windows API's are all perfectly happy with these.  only the
command parser insists on \ as a path delimiter.