Thread: 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.