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.