I've got a table with a column of telephone numbers that have been
called, and a tariff table with tariff codes and the number that
those tariffs apply to.

The number in the tariff table is the first some number of digits of
telephone numbers.

I want to pull out the relevant tariff code for each telephone number
called.

I'd'a thought that should be easy, but damned if I can get it to
work. Can anyone help me out on this one please.

Regards
Mark

Hi Mark,

May be it is easier to help if you post the relevant parts of your real table
structures, some example data and also some of your tries.

If all your fields are of character type, then use left(), substr(),
(r|l|all)trim() etc. You also could use some user defined function to separate
the relevant characters/digits.

RushMore is Your Friend.

Make an index that matchs whats forming in the substring.

then do another query based on that substring.

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

I'm sorry if it was confusing, I think these are the relevant parts
of the tables.

Yes, they're all strings. Telephone numbers, numerics only, not that
it matters I think.

The numbers in the tariff table are strings, just numbers.
The tariff table has numbers in it like "0871", or "079523". They're
not all the same length so "LEFT" and "SUBSTR" and the like aren't
easily used..

And the calls table has telephone numbers like "01234566789",
"0871233454".

Regards
Mark

Hi Mark,

SELECT Numbers.number, Tariffs.code ;
FROM Numbers LEFT OUTER JOIN Tariffs ON Numbers.number = trim(Tariffs.number) ;
WHERE !empty(Tariffs.number)

But how can I do that when the substring is of variable length?

In article , Swdev2 wrote:

Hi Bernard,

Thanks for that.

Yes, I knew the longstring = shortstring thing (it seems to work the otherway
around too), that's why I was confused that I wasn't getting anywhere. I think I
must've not trimmed the shortstring field, I'm either getting old or not drinking
enough coffee.

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.

Regards
Mark

In article <#aSNhNBFJHA.4712@TK2MSFTNGP02.phx.gbl>, Bernhard Sander wrote:

Hi Mark,

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