get rid of invalid chars from phone number field.

[Lionell]

Greetings,

What is the easy way to get rid of Invalid chars from string(return number
only)?

VBA:
Function GetRidInvalidChars(Str As String) As String
' get rid of dashes and invalid chars
For i = Len(Str) To 1 Step -1
If InStr("0123456789", Mid$(Str, i, 1)) = 0 Then
Str = Left$(Str, i - 1) & Mid$(Str, i + 1)
End If
Next

GetRidInvalidChars = Str
End Function

[Joe Celko]

UPDATE Foobar
SET phone_nbr
= REPLACE(phone_nbr,'-', '');

You can nest REPLACE() functions to get rid of parens, etc.

Then go back and add a constraint to the column to keep the data clean.
Did you know the ISO standard for a phone number is 15 digits?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Why are you asking a VB question on a sql server list?

Instead of looping, you can use a couple Replace statements.

Bob Barrows
"Lionell" <Lion...@hotmail.com> wrote in message
news:OV5kiImxCHA.1420@TK2MSFTNGP12...

[Anith Sen]

Here are some general methods which you can use for such string replacement
requirements.

1. Using REPLACE

A very common method is to use multiple REPLACE functions in your UDPATE
statement. This is simple, easy to understand, but may become clumsy, if
you have too many invalid characters in your string

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '044-(212)-976-8789'
SELECT REPLACE(
REPLACE(
REPLACE(@BadStr,
'-', SPACE(0)),
'(', SPACE(0)),
')', SPACE(0))

2. CASE expressions

If the string is limited in length, you can use a series of CASE
expressions,
however this also suffers from the drawbacks described above.

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '7%6@5'

SELECT CASE WHEN SUBSTRING(@BadStr, 1, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 1, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 2, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 2, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 3, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 3, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 4, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 4, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 5, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 5, 1) ELSE '' END

3. T-SQL UPDATE extn.

Another intuitive way of doing such conversions is to use another table
with values to be replaced and corresponding new values as the columns like:

SELECT *
INTO #temp
FROM (
SELECT ' ', SPACE(1) UNION ALL
SELECT '*', SPACE(0) UNION ALL
SELECT '%', SPACE(0) UNION ALL
SELECT '-', SPACE(0) UNION ALL
SELECT '$', SPACE(0) -- add as many as needed
) D(old, new)

Now do:

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '345%*6%3$6-457'
UPDATE #temp
SET @BadStr = REPLACE(@BadStr, Old, New)
PRINT @BadStr

You can make this a permanent table and use it for general data cleanup
routines and can come in handy with a UDF in SQL 2000.

4. Scalar UDF

In SQL 2000, you can use a scalar UDF which can remove all the expleteves
from your input string as follows:

CREATE FUNCTION dbo.ufn_onlydigits (@StrVal AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @StrVal) > 0
SET @StrVal = REPLACE(@StrVal,
SUBSTRING(@StrVal,PATINDEX('%[^0-9]%', @StrVal),1),'')
RETURN @StrVal
END
GO

Usage:
SELECT dbo.ufn_onlydigits('8asdf7%87^A8876-*S')

In other SQL versions, depending on what you are doing, you can also make
the above logic into a stored procedure with an OUTPUT parameter.

--
- Anith
(Please respond only to newsgroups)

[lionel]

Thanks Anith!!!

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:#Y#uMEuxCHA.1268@TK2MSFTNGP10...