Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e67HZRW67150 for ; Fri, 7 Jul 2000 13:35:27 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id NAA10968; Fri, 7 Jul 2000 13:33:58 -0400 (EDT) To: brianb-pgsql@edsamail.com cc: pgsql-sql@postgresql.org, Bruce Momjian Subject: Re: Search for underscore w/ LIKE In-reply-to: <20000707090925.3041.qmail@mail01.edsamail.com.ph> References: <20000707090925.3041.qmail@mail01.edsamail.com.ph> Comments: In-reply-to brianb-pgsql@edsamail.com message dated "Fri, 07 Jul 2000 09:09:24 +0000" Date: Fri, 07 Jul 2000 13:33:58 -0400 Message-ID: <10965.962991238@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/80 brianb-pgsql@edsamail.com writes: > How do I use LIKE to search for strings with an underscore? The > documentation (well, Bruce's book) says to use 2 underscores (__) but it > doesn't work. If Bruce's book says that, I hope it's not too late for him to change it ;-) The correct way is to escape the underscore with a backslash. You actually have to write two backslashes in your query: select * from foo where bar like '%\\_baz' The first backslash quotes the second one for the query parser, so that what ends up inside the system is %\_baz, and then the LIKE function knows what to do with that. Similarly, '\\%' would be the way to match a literal %. You can actually backslash-quote any single character this way in LIKE, but % and _ are the only ones where it makes a difference. (In the regexp-matching operators there are many more special characters and so many more times when you need the backslash trick.) regards, tom lane