Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.2/8.9.1) with ESMTP id FAA63939 for ; Mon, 29 Mar 1999 05:32:58 -0500 (EST) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 55B524B4A9; Mon, 29 Mar 1999 12:32:44 +0200 (IST) Received: from [147.233.148.145] (1169.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA6B01; Mon, 29 Mar 1999 12:34:30 +0200 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: References: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 29 Mar 1999 12:34:14 +0200 To: Richi Plana From: Herouth Maoz Subject: Re: [GENERAL] Determining if "in a text set" Cc: pgsql-sql@postgreSQL.org At 20:56 +0200 on 28/03/1999, Richi Plana wrote: > > 1) I'm not sure the regex thing is ANSI SQL and would port to other SQL > systems easilly That's true. ANSI only has LIKE, but it's oh-so-limited. > 2) Isn't there a way to do [^{ws}](word)[{ws}$], where [^{ws}] means > either the start or a whitespace and [{ws}$] means either a whitespace or > EOL? Hehe... Actually, there is. This is what I came up with: SELECT * FROM {class} WHERE '{phrase}' ~ ( '(^|[[:space:]])' || ( rtrim( FieldN ) || '([[:space:]]|$)' ) ); It seems a bit complicated. The [[:space:]] combination is the set of all whitespace characters. Like perl's \s, but locale-sensitive. The combination '(^|[[:space:]])' uses the regexp alternatives syntax. This means "Either the beginning of the phrase (^) or a space character". The combination '([[:space:]]|$)' is, along the same lines, "either a space character or the end of the phrase". All the rest of the mess are concatenation operators, and the parentheses required for their precedence and associativity. > 3) When I try the concat strings operator (||), I get the following psql > error: Yes, you are right about the precedence (I don't know why I didn't encounter it yesterday). One must also remember that the || operator is also non-associative (no real reason for it - it's a minor bug), so you have to put parentheses around it if you concat more than two strings. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma