Replacing a scalar valued function with a Select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Replacing a scalar valued function with a Select

Author

Topic

AllanRidley
Starting Member

5 Posts

Posted - 2012-03-08 : 09:55:36

Hi All

I'm doing a development where one of the requirements is that we are able to identify (i.e. make a guess at) the mobile service provider of a mobile phone number as it is entered. The way I've currently implemented this is by using Regex expressions (from a function I imported into the database) and the unique regex for each of the providers for the countries that we sell into. I also have a table where APN settings for each of the mobile providers is stored. This system has been working well, but it occurred to me today that it would be "neater" (and also more obvious for any providers added) if regexes used to identify the mobile providers where stored in the same table along with the APN settings.

Is there anyway I can do this Select? I've tried a few things to no avail. To give you a bit of a picture here is the simplified schema:

Module Table
============
ID
MobileNumber (varchar)

Provider Table
==============
ID
Provider
Regex

And some simplified data:
Module Table
=============
ID MobileNumber
1 44111111
2 44222222

APN Table
=========
ID Provider Regex
1 EG1 ^441\d+$
2 Another ^442\d+$

My existing function (ServiceProvider) looks something like this:

ALTER FUNCTION [dbo].[ServiceProvider]
(@PhoneNumber as varchar(30))
RETURNS int
BEGIN
IF(dbo.RegexMatch(@PhoneNumber, '^441\d+$') = 1)
BEGIN
RETURN 1/*EG1 */
END
ELSE IF(dbo.RegexMatch(@PhoneNumber, '^441\d+$') = 1)
BEGIN
RETURN 2/*Another */
END
END

I'm able to match the provider to the numbers stored in the Modules table by the following statement:

SELECT Module.ID, APN.Provider
FROM Module
INNER JOIN APN on APN.ID = dbo.ServiceProvider(Module.MobileNumber)

This works fine. As I mentioned, I'd prefer to use the regex stored in the APN table rather. Any way I can do this? Is this explanation as clear as mud?

Thanks in advance.

Allan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:27:37

you need to alter the function like below


ALTER FUNCTION [dbo].[ServiceProvider] 
(@PhoneNumber as varchar(30),
@regpattern varchar(30))
RETURNS int
BEGIN 
DECLARE @Ret int

SELECT @Ret =ID
FROM APN
WHERE dbo.RegexMatch(@PhoneNumber, Regex) = 1

RETURN @Ret
END 

and then use it like 

SELECT ID, dbo.ServiceProvider(MobileNumber)
FROM Module

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AllanRidley
Starting Member

5 Posts

Posted - 2012-03-08 : 10:44:14

Brilliant. Thanks Visakh! I've got that to work. It turns out I might not be able to use it though, because it's about 11 times slower than my previous query. I'll see what I can figure out, though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:57:03

whats the amount of data in Module table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AllanRidley
Starting Member

5 Posts

Posted - 2012-03-08 : 11:17:14

Just over 1000 records, increasing at about 100 per month. There are about 30 records in the Providers table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 11:46:06

quote:
Originally posted by AllanRidley

Just over 1000 records, increasing at about 100 per month. There are about 30 records in the Providers table

why not add it as a field in table and update it one time using below logic and there after just retrieve it when needed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources