how to remove ''bad'' characters from field?

Question

Post reply

how to remove ''bad'' characters from field?

Andy Gelband

SSC Enthusiast

Points: 124
More actions
January 18, 2007 at 4:46 pm

#115898

I'm trying to manipulate a field (with thousands of rows) that contains telephone numbers so that I can make them diallable....
The field contains stuff like +44 020 7, but sometimes + 44 (0) 207 etc ad infinitum.
If I could clear out all spaces, commas, plus signs etc to just get 440207.... the rest would be easy.
Any ideas?
Thanks very much!
Andy

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

REPLACE function is for your service.

BTW, "+ 44 (0) 207... " must turn into "44207..." or "0207..."

That zero to be dialed only if you are calling from UK. And +44 to be dialed only if you are calling from another country.

Right?

_____________
Code for TallyGenerator

MohammedU SSCoach Points: 19644 More actions · Answer 2

You can create a function and update the table using function...

you can use the function from the following url..

http://sqljunkies.com/Forums/ShowPost.aspx?PostID=11222

MohammedU
Microsoft SQL Server MVP

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Depends on how it is in UK at the moment... we had a major change a few years ago in Czechia. Before, you had to dial 0 in front of a number when calling another city (and large cities had different number of digits than small ones). Now we have 9 digit numbers everywhere and no leading zeros when dialling a Czech number.

Also, when calling from a mobile phone, country code can be part of the number - no matter where are you calling from (also inside the country).

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

Some of the solutions on the link seem, well... a bit bulky... I don't remember where I got it from but this works and it's nasty fast...

USE NorthWind

GO

CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @pos INT

SET @Pos = PATINDEX('%[^0-9]%',@Input)

WHILE @Pos > 0

BEGIN

SET @Input = STUFF(@Input,@pos,1,'')

SET @Pos = PATINDEX('%[^0-9]%',@Input)

END

RETURN @Input

END

GO

--===== Demo the function with just about every key on a keyboard (except caps)

SELECT '1qaz2wsx3edc4rfv5tgb6yhn7ujm8ik,9ol.0p;/-[''=]\!@#$%^&*()_+|{}:"<>?',

dbo.RemoveChars('1qaz2wsx3edc4rfv5tgb6yhn7ujm8ik,9ol.0p;/-[''=]\!@#$%^&*()_+|{}:"<>?')

--===== Demo the function with some pretty well screwed up numbers

SELECT Phone AS OriginalPhone,dbo.RemoveChars(Phone) AS CleanPhone

FROM Customers

GO

DROP FUNCTION dbo.RemoveChars

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 5

The problem here is beyond of SQL. THe problem is in logic.

Those "bad" characters not suppose to be removed at all.

+44 means international code. It supposed to appear only when you call from abroad location.

(0) means optional "operator" code which suppose to apper only if you dial from UK but from another city.

And all those characters are required by auto-dialing tools to choose right way to dial the number.

By removing them you just spoil the data in your database.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

Yep... I agree with Serquiy... if it's for an automatic dialer (or ever could be) or for humans to actually know whether it might be an international call just by looking at it, either leave the formatting alone or, if you absolutely have to strip non-numeric characters for some reason, do it in a new column.

--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: 1004412 More actions · Answer 7

If you have a Tally table, here's a possible solution, as well...

CREATE FUNCTION dbo.StripNonDigit(@Input VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Result VARCHAR(8000)

SELECT @Result = ISNULL(@Result,'')

+ SUBSTRING(@Input,N,1)

FROM dbo.Tally

WHERE SUBSTRING(@Input,N,1) LIKE '[0-9]'

AND N <= LEN(@Input)

RETURN @Result

END

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

You forgot

ORDER BY N

--------

BTW, your Tally table is an implementation of iteration step = 1 without hardcoding it.

See? It works!

_____________
Code for TallyGenerator

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

Don't need it... N is the clustered Primary Key. Yeah, I know what you're going to say...

Anyway, the length in the substrings is hardcoded... surely you're not suggesting that I do what you did in another post...

DECLARE @One TINYINT

SET @One = 1

--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)