Received: from olympus.scw.org (209-9-156-98.sdsl.cais.net [209.9.156.98]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB801hP12160 for ; Thu, 7 Dec 2000 19:01:43 -0500 (EST) (envelope-from jburton@scw.org) Received: from dionysus (arachne.scw.org [209.9.156.106]) by olympus.scw.org (8.10.1/8.10.1) with ESMTP id eB801Jg24476; Thu, 7 Dec 2000 19:01:19 -0500 From: "Joel Burton" Organization: Support Center of Washington To: Beth Gatewood Date: Thu, 7 Dec 2000 19:01:16 -0800 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: Re: trying to pattern match to a value contained in a column CC: pgsql-sql@postgresql.org Message-ID: <3A2FDE7C.23324.552F36@localhost> In-reply-to: <3A3004DC.4FF7CB37@mbt.washington.edu> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200012/53 X-Sequence-Number: 384 > This makes perfect sense...unfortunately it isn't working... > > I hope this isn't because I am using 6.3 (yes...I know it is very very > old but this is currently where the data is!) > > here is the query: > > select * from av34s1 where chromat ~~ ('%' || sample || '%'); > > > ERROR: parser: syntax error at or near "||" > > I have also tried using LIKE.... > > samething.. > > NOW.. > select * from av34s1 where chromat~sample; > > ERROR: There is no operator '~' for types 'bpchar' and 'bpchar' > You will either have to retype this query using an explicit > cast, > or you will have to define the operator using CREATE OPERATOR > The suggestion works fine w/recent versions; perhaps it's a '6.3 thing' Perhaps SELECT * FROM tbl WHERE chromat::TEXT ~~ ('%' || sample || '%' )::TEXT; ? Also, upgrading isn't difficult in most cases; you can pg_dumpall and upgrade and restore your files. 7.0 has many nice features over the 6.x series. -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)