deleting hyphens in a phone field

I am trying to delete hyphens and spaces from a field containing phone
numbers. I know that the "Replace" Function should allow me to do this but
I
don't know how. Every phone number is different, so I don't have a 1st valu
e
to put into the arguments. I am very new to SQL Server and this is driving
me nuts.I don't understand what you mean by '1st value'. You can nest REPLACE
functions to remove both spaces and hyphens in one pass:
UPDATE dbo.MyTable
SET Phone = REPLACE(REPLACE(Phone, '-',''), ' ', '')
Hope this helps.
Dan Guzman
SQL Server MVP
"John H." <John H.@.discussions.microsoft.com> wrote in message
news:03E99E25-D30F-4C4E-839D-F25015B38FA2@.microsoft.com...
>I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this
> but I
> don't know how. Every phone number is different, so I don't have a 1st
> value
> to put into the arguments. I am very new to SQL Server and this is
> driving
> me nuts.|||John H. a écrit :
> I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this bu
t I
> don't know how. Every phone number is different, so I don't have a 1st va
lue
> to put into the arguments. I am very new to SQL Server and this is drivin
g
> me nuts.
This function :
/ ****************************************
***********************************
*/
-- delete all undesirable chars
/ ****************************************
***********************************
*/
-- exemple : FN_RESTRICT('_ Paris...?', 'abcdefghijklmnopqrstuvwxyz')
=>'aris'
CREATE FUNCTION F_RESTRICT (@.IN VARCHAR (8000),
@.CHARSOK VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
IF @.IN IS NULL
RETURN NULL
IF @.CHARSOK IS NULL
RETURN NULL
IF LEN(@.IN) = 0
RETURN @.IN
-- initialisation
DECLARE @.I INTEGER
DECLARE @.OUT VARCHAR(8000)
SET @.OUT = ''
-- lecture caractère par caractère
SET @.I =1
WHILE @.I <= LEN(@.IN)
BEGIN
IF PATINDEX('%' + SUBSTRING(@.IN, @.I, 1)+ '%', @.CHARSOK) > 0
SET @.OUT = @.OUT + SUBSTRING(@.IN, @.I, 1)
SET @.I = @.I + 1
END
RETURN @.OUT
END
GO
Use it in a trigger :
UPDATE MyTable
SET PHONE_NUMBER = F_RESTRICT (PHONE_NUMBER, '0123456789')
You will have a column with only figures in it.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql