Scalar Function - Format Numbers

Question

Scalar Function - Format Numbers

whymaravele

SSC Enthusiast

Points: 119
More actions
June 22, 2016 at 8:52 am

#327387

Hi
I have this function
CREATE FUNCTION [dbo].[fnFormatMobileNumber] (@phone VARCHAR(15))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Formatted VARCHAR(15)
IF (LEN(@phone) > 10)
SET @Formatted = '0' + SUBSTRING(@phone,3,9)
ELSE SET @Formatted = @phone
RETURN @Formatted
END
select
[dbo].[fnFormatMobileNumber](0842507889)
I get 842507889 back when I execute this, and I'm expecting 0842507889, please help

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

whymaravele (6/22/2016)
Hi
I have this function
CREATE FUNCTION [dbo].[fnFormatMobileNumber] (@phone VARCHAR(15))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Formatted VARCHAR(15)
IF (LEN(@phone) > 10)
SET @Formatted = '0' + SUBSTRING(@phone,3,9)
ELSE SET @Formatted = @phone
RETURN @Formatted
END
select
[dbo].[fnFormatMobileNumber](0842507889)
I get 842507889 back when I execute this, and I'm expecting 0842507889, please help

The length of the string you provided is less than 10, so you're getting back what you gave it (SET @Formatted = @phone) as your return value.

What is it that you want to accomplish other than what you've written? It may be possible to convert this from a scalar function (which are notoriously slow) to an ITVF.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

whymaravele SSC Enthusiast Points: 119 More actions · Answer 2

But 0842507889 = 10 digits.. 0 8 4, 2 5 0, 7 8 8 9 those are ten digits

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

whymaravele (6/22/2016)
But 0842507889 = 10 digits.. 0 8 4, 2 5 0, 7 8 8 9 those are ten digits

My mistake - I should have said that the value is not greater than 10 characters. Your IF is checking the if the parameter length is > 10, not >= 10.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ed Wagner (6/22/2016)
whymaravele (6/22/2016)
Hi
I have this function
CREATE FUNCTION [dbo].[fnFormatMobileNumber] (@phone VARCHAR(15))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Formatted VARCHAR(15)
IF (LEN(@phone) > 10)
SET @Formatted = '0' + SUBSTRING(@phone,3,9)
ELSE SET @Formatted = @phone
RETURN @Formatted
END
select
[dbo].[fnFormatMobileNumber](0842507889)
I get 842507889 back when I execute this, and I'm expecting 0842507889, please help
The length of the string you provided is less than 10, so you're getting back what you gave it (SET @Formatted = @phone) as your return value.
What is it that you want to accomplish other than what you've written? It may be possible to convert this from a scalar function (which are notoriously slow) to an ITVF.

You're passing a number to the function. Here's a simplified model:

DECLARE @phone VARCHAR(15) = 0842507889

SELECT @phone

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

If you are ever planning on using this UDF against data in a table (as opposed to onesy stuff passed in from an app) then do NOT solve this problem in this manner!! Scalar UDFs (and multi-statement TVFs) are HORRIBLY BAD in MANY WAYS!! Either use an Inline TVF or inline the logic directly into your statements.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

As mentioned, no need for scalar functions as they perform really bad in queries. A CASE statement will work just fine and it's not really lengthy.

SELECT CASE WHEN LEN(phone) > 10 THEN '0' + SUBSTRING(phone,3,9) ELSE phone END

FROM (VALUES('01234567'),

('012345678'),

('0123456789'),

('01234567890'),

('012345678901'),

('0123456789012'),

('01234567890123'))x(phone)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

whymaravele SSC Enthusiast Points: 119 More actions · Answer 7

I'm trying to validate the phone numbers.

In South Africa we have 10digits numbers. My table has different kind of numbers, Landline, Mobile numbers starting 0(then 9 digits) other starting with 27 instead of 0, and finanlly invalid numbers.

First I want change all numbers starting with 27 and replace it with 0.

Then check for landline numbers to discard them, our landline numbers is anything below 060, meaning any number starting with 060 it's a mobile number.

Then from there change all the valid number and change them back to start with 27.

The purpose of that UDF was so solve this problem.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Your function doesn't do all the validations that you mention. Are you aware of that?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 9

Hi ,

your mobile number count is just 9 degits. that's why their is no change.

declare @phone VARCHAR(15) = '842507889'

BEGIN

DECLARE @Formatted VARCHAR(15)

IF (LEN(@phone) > 10)

SET @Formatted = '0' + SUBSTRING(@phone,3,9)

ELSE SET @Formatted = @phone

print 'your mobile number count is ' + cast(len(@phone) as varchar(10))

print @Formatted

END

hicksl SSC Rookie Points: 48 More actions · Answer 10

Because you are passing the telephone number in as an int, so it drops the leading zero, and you should pass a string in as a string

select

[dbo].[fnFormatMobileNumber]('0842507889')