X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C951D52BBC for ; Mon, 25 Jul 2005 14:48:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40988-04 for ; Mon, 25 Jul 2005 17:48:56 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 6978252B81 for ; Mon, 25 Jul 2005 14:48:54 -0300 (ADT) Received: (qmail 24400 invoked by uid 500); 25 Jul 2005 17:49:52 -0000 Date: Mon, 25 Jul 2005 12:49:52 -0500 From: Bruno Wolff III To: Christian Rusa Cc: pgsql-sql@postgresql.org Subject: Re: Closes Match Message-ID: <20050725174952.GA23263@wolff.to> Mail-Followup-To: Bruno Wolff III , Christian Rusa , pgsql-sql@postgresql.org References: <42E521DF.4000804@rusa.at> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42E521DF.4000804@rusa.at> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/224 X-Sequence-Number: 22202 On Mon, Jul 25, 2005 at 19:31:11 +0200, Christian Rusa wrote: > Hi there, > > I want to match a string against the database and get the closest > (shorter) match. > I was asking myself if its possible to realise this in SQL. > > So if the string is: > abcdefg > > And in the database are entries like: > a > ab > abc > > I just want to get back 'abc'. > > I looked around in the internet and the documentation but found no solution. You could ORDER BY the length of the strings descending and use LIMIT to return only one row. You can write a regular expression to do the matching. Something like: ^(a(b(c(d(e(fg?)?)?)?)?)?$