Re: 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?
>
> Christine Penner
> Ingenious Software
> 250-352-9495
> christine(at)ingenioussoftware(dot)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(dot)klaver(at)gmail(dot)com