BEST MATCH Query

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
prasannaaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TroyKConnect With a Mentor Commented:
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
 
xxg4813Commented:
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
 
Scott PletcherSenior DBACommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
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
 
ispalenyCommented:
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
 
CleanupPingCommented:
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
 
monosodiumgCommented:
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
All Courses

From novice to tech pro — start learning today.