On Fri, Mar 11, 2005 at 05:19:35PM +0000, William Shatner wrote: > I have recently migrated from MS Access to PostgreSQL.Previously I had > a SQL command > > ResultSet aGroupResultSet = aGroupPathStmt.executeQuery( > "SELECT \"groupID\",\"fullpath\" FROM \"groups\" WHERE > \"fullpath\" Like '" + > aPath + "'"); > > where aPath was equal to 'folder\another folder\%'. > > The field to be edited stores the full path in the format > 'folder\folder1\folder2' and so on... > The purpose being to change all groups at this level of the > hieracarchy and below, this was achieved using the '%' in Access, this > however doesn't seem to work in PostgreSQL, it doesn't error out but > it just seems to see the '%' as a normal character.
You're running into problems with \ being the escape character in string constants and again in patterns. There's some discussion of this in the "Pattern Matching" section of the "Functions and Operators" chapter in the documentation: http://www.postgresql.org/docs/8.0/interactive/functions-matching.html Here are some ways to make it work (dollar quoting available only in 8.0 and later): fullpath LIKE 'folder\\\\another folder\\\\%' fullpath LIKE 'folder\\another folder\\%' ESCAPE '' fullpath LIKE $$folder\\another folder\\%$$ fullpath LIKE $$folder\another folder\%$$ ESCAPE '' -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
