| Re: Substring queries, shouldn't this be easy? |
|
 |
|
 |
|
 |
|
 |
Group: microsoft.public.fox.vfp.queriessql · Group Profile
Author: Bernhard SanderBernhard Sander Date: Sep 12, 2008 11:11
Hi Mark,
> That'll be good enough for what I want, but what about the situation where I've got
> both (eg) 087 and 0871 in the tariff table? I can imagine doing it with a coupla
> extra queries (looking for the longest matching tariff for any numbers that turn up
> in "two" tariffs), that certainly wouldn't be bad if it was the only way.
I think you can do it with a subquery. Take this idea as a starting point:
SELECT Numbers.number, Tariffs.code ;
FROM Numbers LEFT OUTER JOIN Tariffs ON Numbers.number = trim(Tariffs.number);
WHERE !empty(Tariffs.number);
AND NOT EXIST ;
(SELECT * ;
FROM Tariffs AS iT ;
WHERE iT.number = trim(Tariffs.number) ;
AND len(trim(iT.number)) > len(trim(Tariffs.number));
)
In words: exclude all result rows if there exists a longer Tariff number.
Regards
Bernhard Sander
| 
