Stripping all non-numeric chars from phone number and then formatting it.

Question

Stripping all non-numeric chars from phone number and then formatting it.

tsmith-960032

SSCrazy

Points: 2518
More actions
July 17, 2009 at 9:43 pm

#207101

Hi. I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off a stored procedure, and we do not want to touch it. What I am looking for is a way to correctly format a phone number at runtime.
The source of the number does not control user input. Therefore the user could put in various characters either by accident or because he/she is used to a particular way of presenting phone numbers. So, the problem is twofold.
1. I need to strip the phone number of any non-numeric characters.
2. Present the phone number as (123) 456-7890
On the number 2, my solution is
="(" & Mid("1234567890",1,3) & ") " & Mid("1234567890",4,3) & "-" & Mid("1234567890",7,4)
I am left with the problem of how to strip the number of all non-numeric characters from within the textbox and/or table cell.
Any ideas would be greatly appreciated.
Thank you.

Viewing 2 posts - 1 through 1 (of 1 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

tsmith (7/17/2009)
Hi. I would like to add a problem that we have at work. Writing a query is not an option. The report we need to generate runs off a stored procedure, and we do not want to touch it. What I am looking for is a way to correctly format a phone number at runtime.
The source of the number does not control user input. Therefore the user could put in various characters either by accident or because he/she is used to a particular way of presenting phone numbers. So, the problem is twofold.
1. I need to strip the phone number of any non-numeric characters.
2. Present the phone number as (123) 456-7890
On the number 2, my solution is
="(" & Mid("1234567890",1,3) & ") " & Mid("1234567890",4,3) & "-" & Mid("1234567890",7,4)
I am left with the problem of how to strip the number of all non-numeric characters from within the textbox and/or table cell.
Any ideas would be greatly appreciated.
Thank you.

Double post... solution is at the following URL... please post any additional solutions there.

http://qa.sqlservercentral.com/Forums/Topic698934-338-1.aspx#bm755226

--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)
Intro to Tally Tables and Functions