function to clean a phonenumber

Question

function to clean a phonenumber

ninamahezi

SSC Veteran

Points: 212
More actions
October 25, 2016 at 6:44 am

#331582

Hello,
I want a sql function that will allow to make one update but with several replace on the same column.
For exemple
Replace the space by vacuum('')
The slash by vacuum
The point by vacuum
The dash by vacuum
The comma with the empty
The "+33 " by 0
thanks for your help

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

ninamahezi (10/25/2016)
Hello,
I want a sql function that will allow to make one update but with several replace on the same column.
For exemple
Replace the space by vacuum('')
The slash by vacuum
The point by vacuum
The dash by vacuum
The comma with the empty
The "+33 " by 0
thanks for your help

Here's a sample of what you can do to get your result.

DECLARE @Sample TABLE(

phone varchar(20)

)

INSERT INTO @Sample

VALUES ('+33-123-861-7321'),

('+52.168.945.5465'),

('+84 (854)933,4186')

SELECT *

FROM @Sample

CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '/', ''), '.', ''), '-', ''), ',', ''), '+33', '0'))x(cleanphone)

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

ninamahezi SSC Veteran Points: 212 More actions · Answer 2

thanks very much for your help

I need a function with a return statement and the parameter will be a phonenumber

Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

ninamahezi (10/25/2016)
thanks very much for your help
I need a function with a return statement and the parameter will be a phonenumber
Thanks

Then create it. Just remember that scalar user defined functions are bad for performance. You shouldn't store formatted phone numbers, the format should be a display option and not part of the data.

Here's another option for you if you really want a function:

http://www.sqlservercentral.com/scripts/T-SQL/117890/

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

ninamahezi SSC Veteran Points: 212 More actions · Answer 4

thanks,

I have never writen sql function. So I do not know what is.

What we want to do is make an update on the phonenumber taking into account all these replace.

the goal was to create a function that will perform this task.

Thanks for your help

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Functions in SQL don't perform tasks. Functions return values, that's their main purpose in any language.

To perform a process, you need a stored procedure. To update the values in a table, you need an UPDATE statement.

If you're not sure on how to do that, be sure to have someone that can help you or you might lose your data. The syntax for UPDATE can be found here: https://msdn.microsoft.com/en-us/library/ms177523.aspx or you could just press F1 when selecting the keyword on Management Studio (SSMS).

You can also find a good start on writing T-SQL code in here: http://www.sqlservercentral.com/stairway/75773/

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

ninamahezi SSC Veteran Points: 212 More actions · Answer 6

I know how to use UPDATE but I was talking about functions in SQL.

Thanks very much.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

ninamahezi (10/25/2016)
I know how to use UPDATE but I was talking about functions in SQL.
Thanks very much.

You have your answer. Functions do not perform updates in T-SQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Luis Cazares (10/25/2016)
ninamahezi (10/25/2016)
Hello,
I want a sql function that will allow to make one update but with several replace on the same column.
For exemple
Replace the space by vacuum('')
The slash by vacuum
The point by vacuum
The dash by vacuum
The comma with the empty
The "+33 " by 0
thanks for your help
Here's a sample of what you can do to get your result.
DECLARE @Sample TABLE(
phone varchar(20)
)
INSERT INTO @Sample
VALUES ('+33-123-861-7321'),
('+52.168.945.5465'),
('+84 (854)933,4186')
SELECT *
FROM @Sample
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, ' ', ''), '/', ''), '.', ''), '-', ''), ',', ''), '+33', '0'))x(cleanphone)

That works nice and fast thanks to the nested REPLACEs but I'd bet the OP wants to replace the "+" number (country code?) no matter what the number may be. Country Codes can be 1 to 3 digits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

ninamahezi (10/25/2016)
thanks,
I have never writen sql function. So I do not know what is.
What we want to do is make an update on the phonenumber taking into account all these replace.
the goal was to create a function that will perform this task.
Thanks for your help

Functions can't do an update but they can be used as a source in an update.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 10

>> I want a SQL function that will allow to make one update but with several replace on the same column. <<

UPDATE Foobar -- we have no DDL or even a table name!

SET phone_nbr

= REPLACE(

REPLACE(

REPLACE(phone_nbr, ' ', ''),

'/', ''),

'.', ''),

'-', ''),

',', ''),

'+33', '');

SQL is a declarative language. Nesting functions inside each other can be quite fast and easy for the optimizer. If you hang out with LISP programmers, this will seem quite natural 🙂

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

x SSC-Insane Points: 23660 More actions · Answer 11

CELKO (10/26/2016)
>> I want a SQL function that will allow to make one update but with several replace on the same column. <<
UPDATE Foobar -- we have no DDL or even a table name!
SET phone_nbr
= REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(phone_nbr, ' ', ''),
'/', ''),
'.', ''),
'-', ''),
',', ''),
'+33', '');
SQL is a declarative language. Nesting functions inside each other can be quite fast and easy for the optimizer. If you hang out with LISP programmers, this will seem quite natural 🙂

Plus we can use PROGN with SETQ's and do punched card programming!

edit: heh I don't really know lisp