asked on

SQL code to format numbers to mobile phone numbers

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:
MbPhon
0401444333
0401 000111
0416555333
etc

* 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

JoeNuvo

not sure if this is the best method,

and also, i'm not very good regular expression.

just give it a try.

with new created function below.

SELECT
CASE dbo.fn_regex( '\b(04\d{8}|04\d{8}\D*)\b', LTRIM( Replace(MbPhon,' ','') ) )
WHEN 0 THEN ''
ELSE LEFT(LTRIM( Replace(MbPhon,' ','') ), 10)
END As MbPhon
FROM table1

or

SELECT
LEFT(LTRIM( Replace(MbPhon,' ','') ), 10) As MbPhon
FROM table1
WHERE
dbo.fn_regex( '\b(04\d{8}|04\d{8}\D*)\b', LTRIM( Replace(MbPhon,' ','') ) ) <> 0

--http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx
CREATE FUNCTION	dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
       declare @obj int
       declare @res int
       declare @match bit
       set @match=0

       exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
       IF (@res <> 0) RETURN NULL

       exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
       IF (@res <> 0) RETURN NULL

       exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
       IF (@res <> 0) RETURN NULL

       exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
       IF (@res <> 0) RETURN NULL

       exec @res=sp_OADestroy @obj

       return @match
END

Open in new window

ASKER CERTIFIED SOLUTION

AmmarR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sureshbabukrish

create function fn_FormatPhone(@Phone varchar(20))
returns      varchar(20)
As
Begin

declare @FormattedPhone varchar(30)

set       @Phone = replace(@Phone, '.', '') --alot of entries use periods instead of dashes
set       @Phone = replace(@Phone, ' ', '')
set       @Phone = replace(@Phone, '/', '')
set       @Phone = replace(@Phone, '\', '')

set      @FormattedPhone =
      Case
       When isNumeric(@Phone) = 1 Then
       case
       when len(@Phone) > 10 then ''
       when len(@Phone) < 10 then ''
       else @Phone
       end
       When left(@phone,2) like '[0-9][0-9]' Then @phone
       When left(@phone,10) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' Then @phone
       Else ''
      End
if left(@FormattedPhone,2) <> '04'
begin
      set @FormattedPhone = ''
end

return      left(@FormattedPhone,10)

end

JCTDD

ASKER

thanks a lot for your responses guys!!! I have to test them all out before I get back to you