Answered by:
extracting valid mobile numbers

hi All,
im trying to
extract the valid mobile numbers with out any other charecters(*,/,+ etc..) only numeric 10 digited number.
EX:*091234567890output 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 '919234567890','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
Question
Answers

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.
[Personal Site] [Blog] [Facebook]
 Proposed as answer by Allen Li  MSFTModerator Thursday, November 21, 2013 3:02 AM
 Marked as answer by Allen Li  MSFTModerator Sunday, December 01, 2013 10:57 AM
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('%[^09]%', @strAlphaNumeric) BEGIN WHILE @intAlpha > 0 BEGIN SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^09]%', @strAlphaNumeric ) END END RETURN ISNULL(@strAlphaNumeric,0) END
Usage:
select [dbo].[GetNumericPhone](MOBILE) from @mobile

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.

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 Taherul673 Tuesday, November 19, 2013 7:34 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.
[Personal Site] [Blog] [Facebook]
 Proposed as answer by Allen Li  MSFTModerator Thursday, November 21, 2013 3:02 AM
 Marked as answer by Allen Li  MSFTModerator Sunday, December 01, 2013 10:57 AM