howto perform a partial match?

[ollibr]

I have to set up a query against an sql database which contains
information from our pbx. there is one table (called CALLS) which
contains all the calls made. Another table contains information about
called partners (DESTINATIONS)

I want to do a query on the CALLS table to sort out some calls by
a specific date for example. Additionally I want to resolve the called
number against the information from the DESTINATIONS table.

The issue is that in the DESTINATIONS table some entries exist only for
root numbers of called destinations.

For example when someone calls 920378123 this is stored in the CALLS table field DESTNUM

In DESTINATIONS there is a record

field value
NAME company_xyz
NUM 920378

which matches the called number excluding the pbx extension.

How can I integrate the information from the DESTINATIONS table
into the query on the CALLS table when I want the value of the
NAME field given back by the server?

Any comments are very welcome! thanks! Oliver

 

[swampBoogie]

Assuming that NUM and destnum are numeric. If they are char, remove the cast expressions.

where cast(destnum as varchar(10)) =
substring(cast(NUM as varchar(10)) from 1 for 
char_length(cast(destnum as varchar(10)))

The substring and char_length functions may have a different syntax/name in your dbms.

 

[PHV]

You may also try:
WHERE destnum LIKE num || '%'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swampBoogie]

Yes, that's a better solution than my convoluted expression.