What about this; SELECT * FROM stdcodes WHERE '0018764582' LIKE areacode || '%' order by areacode desc limit 1;
Steve -----Original Message----- From: Lloyd thomas [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 15:00 To: [EMAIL PROTECTED] Subject: Re: [sqlite] Matching telephone strings >I think I have sussed it.< Well not quite!! If I extend the 'select' parameter to include other columns, there are instances where the results in those columns may not be related to what is in the left columns (as I would expect) and if you group the results, you end up with more than one result(albeit the best match seems to be first). The following query seems to sort it out, but is it correct? sql--------------------------------------------------- select MAX(areacode) as areacode, band, destination from stdcodes where '001212' like areacode || '%' group by areacode limit 0,1; ----------------------------------------------------- > I think I have sussed it. If you can see something wrong with the follwing > let me know. > > code------------------------------------------------- > SELECT MAX(areacode) AS areacode FROM stdcodes WHERE '0018764582' LIKE > areacode || '%'; > ----------------------------------------------- > > > > Thanks Kurt, > > The GUI I was using to run the query seems to be using > > an old version of the SQLite DLL. I haved tried another one and the query > > has got me closer to what I want. > > The problem I now have is that I am getting too many results. > > ie. > > SELECT areacode FROM stdcodes WHERE '0018764582' LIKE areacode || '%'; > > > > results: > > 001 USA > > 001876 Jamaica > > > > As you can see the second result is the correct one, is there a way I can > > narrow down the result to the nearest fit? > > > > > > > > I tried the follwing with no results: > > > > > > > > select areacode from stdcodes where '01865323260' like areacode || '%' > > > > > > Don't know what to tell you. Here's an actual query: > > > > > > sqlite> select id from entities where 'tmkxyz' like id || '%'; > > > id > > > ---------- > > > tmk > > > > > > Looks analogous to what you're trying to do. > > > > > > What version of SQLite are you using? > > > > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
