# extracting valid mobile numbers

### Question

• hi All,

im trying to

extract the valid mobile numbers with out any other charecters(*,/,+ etc..) only numeric 10 digited number.

EX:*091234567890--output should be 91234567890

Ex2:091234567890**--91234567890

so dynamically i will get 10 digits in sequence i get but i should consider only 10 digits to output

declare @mobile table(MOBILE varchar(16),Result VARCHAR(10))
insert into @mobile
select '(+91)-9234567890','VALID' union
select '92345678990*','VALID' union all
select '91-9234567890','VALID' union all
select '(91)9234567890','VALID' union all
select '+9234567890*','VALID' union all
select '(9234567890','VALID' union all
select '09234567890' ,'VALID' union all
select '9234567890-','VALID' union all
select '+923456789*' ,'INVALID' union all
select '+234567890*' ,'INVALID' union all
select '97(31)-789889','INVALID' union all
select '+234567890&' ,'INVALID'
--
select * from @mobile

Tuesday, November 19, 2013 6:06 PM

• You can use Regular Expression CLR Function to clear all none numeric data in one time without lopping. Moreover if you find an expression which fit the data you are looking for the you can do it all in one simple function. This will need some thinking maybe & understanding you requirements... i am not sure i do now(step one: clear none numeric is obvious, but the last part i am not sure i got).

Do you want a solution with CLR? If so i can guide you on the C# code (1 line of code) and the installation. Then we will need to find the Regular Expression. It will a better solution then using pure TSQL.

Tuesday, November 19, 2013 9:31 PM

### All replies

• Create a function to strip off all the non numeric characters like below:

```CREATE FUNCTION [dbo].[GetNumericPhone]
(@strAlphaNumeric VARCHAR(20))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @intAlpha BIGINT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
```

Usage:

`select [dbo].[GetNumericPhone](MOBILE) from @mobile`

Tuesday, November 19, 2013 6:29 PM
• this function will help me to some extent to extract the Numeric Numbers.

but Ex:9191234567890 or 091234567890  in this my mobile number part is last 10 digits.

it may be changing to left some time Right.

here one thing we can make sure is it never start with Number <6 mean(starting number will be between [6,9])

my dialer will recognise on 10 digited number.

Tuesday, November 19, 2013 7:07 PM
• Adjust  but tweaking sometime like below:

``` select *,RIGHT([dbo].[GetNumericPhone](RTRIM(MOBILE)),10) from @mobile
Where LEN(RIGHT([dbo].[GetNumericPhone](RTRIM(MOBILE)),10))>=10```

• Edited by Tuesday, November 19, 2013 7:34 PM
Tuesday, November 19, 2013 7:33 PM
• You can use Regular Expression CLR Function to clear all none numeric data in one time without lopping. Moreover if you find an expression which fit the data you are looking for the you can do it all in one simple function. This will need some thinking maybe & understanding you requirements... i am not sure i do now(step one: clear none numeric is obvious, but the last part i am not sure i got).

Do you want a solution with CLR? If so i can guide you on the C# code (1 line of code) and the installation. Then we will need to find the Regular Expression. It will a better solution then using pure TSQL.