Find \ in text

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/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/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.