• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Matching Telephone Numbers To Prefixes

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
badabing1
Asked:
badabing1
  • 4
  • 3
1 Solution
 
DavidMorrisonCommented:
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
 
badabing1Author Commented:
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
 
DavidMorrisonCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
badabing1Author Commented:
Ok, thanks for your help Dave.
0
 
DavidMorrisonCommented:
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
 
badabing1Author Commented:
Question answered regarding performance but script was taken from elsewhere.
0
 
badabing1Author Commented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now