?
Solved

BEST MATCH Query

Posted on 2003-03-07
8
Medium Priority
?
1,218 Views
Last Modified: 2012-05-04
I need in finding the best match.
I have two tables one is zones table with
Zonename and prefixes eg
0044      UK
004477    UK-Mobile
0044207   UK-london

another table where in i have a number 00447766055088

i just want to find the zone name from the zones table.I need a BEST match searching TSQL

Thanks in Advance
prasannaa
0
Comment
Question by:prasannaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Accepted Solution

by:
TroyK earned 200 total points
ID: 8089756
prasannaa;

You didn't specify the algorithm to use for "BEST match", but this script may get you started:

-------- Begin SQL --------
CREATE TABLE zones(
  Zonename varchar(16) NOT NULL,
  Prefix varchar(16) NOT NULL
)
GO

INSERT INTO zones
SELECT '0044', 'UK' UNION ALL
SELECT '004477', 'UK-Mobile' UNION ALL
SELECT '0044207', 'UK-london'
GO

CREATE TABLE another(
  ArgID int NOT NULL PRIMARY KEY,
  Searcharg varchar(16) NOT NULL
)
GO

INSERT INTO another VALUES(1, '00447766055088')
INSERT INTO another VALUES(2, '00442073456790')
GO

SELECT TOP 1 z.Zonename, z.Prefix, DATALENGTH(z.Zonename)
FROM zones z
WHERE (SELECT Searcharg FROM another WHERE argID = 1) LIKE Zonename + '%'
ORDER BY DATALENGTH(z.Zonename) DESC
GO

DROP TABLE another, zones
GO
--------- End SQL ---------

HTH,
TroyK, MCSD
0
 
LVL 4

Expert Comment

by:xxg4813
ID: 8089800
Hi,

This one should work.

--------------------------
create table zones(zonename varchar(20), prefixes varchar(20))

insert into zones values('0044207','UK-London')
....

select top 1 * from zones
where left('00447766055088', len(zonename)) = zonename
order by len(zonename) desc

--------------------------

But it is going to do a table cost. If the table is small, the performance is tolerable.

Good luck!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8090869
Do you want to do this stand-alone/statically, as given above, or do you want to do this dynamically as part of a query on "another" table, for example:

SELECT another.col1, <matching zone name>, another.col3, ...
FROM another
...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8090922
If you do want to do it dynamically, you can do something like the following (assuming that prefix is the numeric code and zonename is the descriptive name):

SELECT *,
     (SELECT ZoneName
     FROM zones z1
     WHERE LEFT(another.number, LEN(prefix)) = prefix AND
     NOT EXISTS (SELECT 1 FROM zones z2
          WHERE LEFT(another.number, LEN(z2.prefix)) = z2.prefix AND
          LEN(z2.prefix) > LEN(z1.prefix))) AS ZoneName
FROM another
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8092193
ScottPletcher's algorithm "left(number, len(zoneid)) = zoneid" is faster than "number like rtrim(zoneid)+'%'" algorithm I tried to use.

select n2.number,z2.zonename
from numbers n2
join zones   z2 on left(n2.number, len(z2.zoneid)) = z2.zoneid
join
(
 select n.number,MLen=max(len(zoneid))
 from numbers n
 join zones   z on left(n.number, len(z.zoneid)) = z.zoneid
 group by n.number
) x on n2.number=x.number and len(z2.zoneid)=x.MLen
0
 

Expert Comment

by:CleanupPing
ID: 9276067
prasannaa:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11042399
No comment has been added to this question in more than 251 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: TroyK http:#8089756

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question