troubleshooting Question

SQL code to format numbers to mobile phone numbers

Avatar of JCTDD
JCTDD asked on
Microsoft SQL Server 2005
4 Comments1 Solution626 ViewsLast Modified:
I need help to create code to format numbers in Table1 to correct mobile number format i.e. 04xxxxxxxx I thought of doing this in a stored procedure but maybe its best to create a function however I hardly create functions so needing help.

Table name = Table1
Field name = MbPhon
datatype is char(20)

need code either an SP or function to format mobile numbers to correct format below
basically need to format so there is:
- no spaces between numbers
- no mobile numbers displayed to output if contain special chars
- must start with 04 and be a maximum of 10 digits (04xxxxxxxx)

Table looks like this:

0401 000111

* Note 0401000111 is just an example mob# used
MbPhon values:
0401000111son should be 0401000111
0401 000 111 should be 0401000111
0401 000111 should be 0401000111
0401000111 (d) should be 0401000111
0401000111 dean should be 0401000111
0401000111 / 0402 should be 0401000111
04010001114 should be disregarded from outputting as there is an extra number on the end
pager 311100 should be disregarded from outputting as doesnt start with 04
02111000 should be disregarded from outputting as doesnt start with 04

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros