CODEWITHSUNDEEP
sqlperformancefunction
Nick Chackowsky
Nick Chackowsky

Reputation: 25

Efficiency of SQL scalar function

I have a three-database application (none developed by me) in which many phone numbers are stored, but their format is inconsistent (555-5555, (555) 555-5555, 5555555555, 5555555, etc.) On the web side, I want them always to be displayed like 555-555-5555. I've written a scalar function to clean them up, but it's going to be called OFTEN and I'm worried about its efficiency.

Can you have a look and please advise me if there's a better way.

ALTER FUNCTION [dbo].[FormatPhone]
(
  @p AS VARCHAR(20)
) RETURNS VARCHAR(12) AS

BEGIN
DECLARE @ret AS VARCHAR(12) = ''
DECLARE @c AS CHAR
DECLARE @i AS INT
DECLARE @stop AS INT

-- loop to grab only digits from @p
SET @i = 1
SET @stop = LEN(@p)
WHILE @i <= @stop
BEGIN
    SET @c = SUBSTRING(@p, @i, 1)
    IF @c >= '0' AND @c <= '9' SET @ret = @ret + @c
    SET @i = @i + 1
END
IF LEN(@ret) = 7 SET @ret = '204' + @ret -- account for forgotten area code

SET @ret = LEFT(@ret, 3) + '-' + SUBSTRING(@ret, 4, 3) + '-' + RIGHT(@ret, 4)

RETURN @ret

Thanks! Nick.

Upvotes: 0

Views: 88

Answers (3)

Phil Raffel
Phil Raffel

Reputation: 11

This function looks at the number/string to determine the size and format, with-out looping

CREATE Function [dbo].[FormatPhone] (@PD varchar(50))
RETURNS Varchar(50)
As 
Begin

Declare @F1 As tinyint
Declare @F2 As tinyint
Declare @F3 As tinyint
Declare @F4 As tinyint
Declare @F5 As tinyint
Declare @F6 As tinyint

Declare @NPD As Varchar(50)

Declare @PDLen As tinyint

--Trim any space off the ends
SET @PD = RTRIM(LTRIM(@PD))

--Default just return the data, unless in proper foramt
SET @NPD = @PD

--Check for phone number that all ready has formatting, 
--we are only going to format a 'Pure' set of numbers.
--Check for formating, space 
SET @F1 = CHARINDEX('(', @PD)
SET @F2 = CHARINDEX(')', @PD)
SET @F3 = CHARINDEX('-', @PD)
SET @F4 = CHARINDEX('.', @PD)
SET @F5 = CHARINDEX(' ', @PD)
SET @F6 = @F1 +@F2 + @F3 + @F4 + @F5


IF @F6 = 0
--No formating, figure out the lenght
BEGIN
SET @PDLen = LEN(@PD)

    IF @PDLen = 10  SET @NPD = '('+SUBSTRING(@PD, 1, 3)+') '+SUBSTRING(@PD, 4, 3)+'-'+SUBSTRING(@PD, 7, 4)
    IF @PDLen = 11 AND SUBSTRING(@PD, 1, 1) = '1'  SET @NPD = '('+SUBSTRING(@PD, 2, 3)+') '+SUBSTRING(@PD, 5, 3)+'-'+SUBSTRING(@PD, 8, 4)
    IF @PDLen = 7  SET @NPD = '(   ) '+SUBSTRING(@PD, 1, 3)+'-'+SUBSTRING(@PD, 4, 4)

END

RETURN @NPD

END

Upvotes: 0

satnhak
satnhak

Reputation: 9861

What you are talking about is data cleansing. In my experience (I had a 6 month project with a Russian business unit that consisted about 90% of bashing my head against the wall on the issue of phone number formats) this is a total pain.

My advice on this is to do it once and do it properly; then put the systems in place to prevent dirty data from entering the system in the future.

What we ended up doing was getting a few girls from the admin office to do it by hand! The problem is that if you do it wrong you make the data useless. Have a go at automated parsing and see where it gets you, but sometimes man beats machine.

Upvotes: 2

realnumber3012
realnumber3012

Reputation: 1062

  • Replace all haracters except numbers
  • Convert into sample XXX-XXX-XXXX

Use SUBSTRING, REPLACE T-SQL

Upvotes: 1

Related Questions