Solved

Matching Telephone Numbers To Prefixes

Posted on 2011-09-08
7
413 Views
Last Modified: 2012-05-12
I have a requirement to match telephone numbers to a prefix within a table of prefixes.  In the instance where there are multiple matches the longest number match should be selected.

Example:
Prefix
0845
08456
08457
084568
084567

0845685746 should match 08456
0845456875 should match 0845

My question is should I perform this task in a VB.net script in the SSIS package which imports the list of calls or should I stage the calls and then run a SQL script to do the matching?

Any help with the script would also be much appreciated.  I am looking for the best performance solution as this may need to deal with millions of calls per import.

Thanks in advance for any help.
0
Comment
Question by:badabing1
[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
  • 4
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
DavidMorrison earned 500 total points
ID: 36502318
If you are after performance do it in SQL, SSIS is slower but you get a much more "row by row" approach, that's the trade off.


the sql should look something like this:

with AllMatches
as
(
      select P.Prefix, len(P.Prefix) as PrefixLen, T.TelephoneNo
      from TelephoneNos as T
      JOIN Prefixes as P on P.Prefix = left(T.TelephoneNo, len(P.Prefix))
)


select AM.Prefix, AM.Telephone
from AllMatches as AM
where AM.PrefixLen = (select max(X.PrefixLen) from AllMatches as X where X.TelephoneNo = AM.TelephoneNo)


thanks

Dave
0
 

Author Comment

by:badabing1
ID: 36509624
Thanks for the response Dave.
I will focus on doing this task in SQL then for performance.  I have been given a neat piece of code, will this be comparible to your script in terms of performance?  In this script the CLI table is being updated with the Prefix table's foreign key, where there is not a match -1 is returned.
UPDATE a
	SET idPrefix = ISNULL((
	SELECT TOP 1 idPrefix
	FROM Prefix AS b
	WHERE a.CLI LIKE b.Prefix + '%'
	ORDER BY LEN(b.Prefix) DESC),-1)
FROM CLI AS a

Open in new window

0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36510241
thats a tidy bit of code, I'd run them side by side to compare performance. I wouldn't imagine there will be much in it either way depending on indexing etc


Thanks

Dave
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:badabing1
ID: 36510264
Ok, thanks for your help Dave.
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36510269
actually, looking at it, the code you have supplied will not be able to use any indexes as the LIKE statement and the string concatenation break the SARGability

If you're tables are correctly indexed I would think my code would run faster



Thanks

Dave
0
 

Author Closing Comment

by:badabing1
ID: 36510308
Question answered regarding performance but script was taken from elsewhere.
0
 

Author Comment

by:badabing1
ID: 36510326
I had just closed the issue when I noticed your additional comment, I have awarded you the full points anyway for the solution.  I will consider using your script over the one I posted above following your last comment.  Thanks.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

690 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