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@xxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
