none
Get only valid 8 digit numbers

    Question

  • Hi Guys,

    I got data in following format

    Number1 Number2 Number3
    12345678 56 NULL
    12345678/12345678 qewq21321314 
    M 12345678 12345678 12345678
    N 12345678 1234567890/1234567890 
    1@!@#$%^&*34567 12345678/123456789 
    1234567890 12345678 12345678


    Now I want ouput as following
    Number1 OPNum1 Number2 OPNum2 Number3 OPNum3
    12345678 12345678 56 null NULL null
    12345678/12345678 12345678/12345678 qewq21321314 21321314   null
    M 12345678 12345678 12345678 12345678 12345678 12345678
    N 12345678 12345678 1234567890/1234567890 null  null
    1@!@#$%^&*34567 Null 12345678/123456789 12345678  null
    1234567890 null 12345678 12345678 12345678 12345678

    I want to remove extract characters other than numbers, for that I created function extract only number based on patindex[0-9].

    Now I need to split following "1234567890/1234567890" and identify valid number which is 8 digit.

    When I did initial pat index I pushed the data to temp table, from temp table I have taken following approach to fetch valid 8 digits.
    select * from (select number1,opnumber1 from #temp where len(opnumber1)=8) a
    full outer join (select number2,opnumber2 from #temp where len(opnumber2)=8) b
    on a.number1=b.number2)

    this way I am fetching all 8 digits, Is there any other better approach then this.

    As I need to fetch again numbers divided by / and find valid 8 digits in that set.

    Regards,
    Navin

    Saturday, October 19, 2013 4:08 AM

Answers

  • Refer the below solution,

    declare @table table(Number1 varchar(30), Number2 varchar(30), Number3 varchar(30) )
    insert into @table values ('12345678','56',NULL)
    insert into @table values ('12345678/12345678','qewq21321314','')   
    insert into @table values ('M 12345678','12345678','12345678') 
    insert into @table values ('N 12345678','1234567890/1234567890','')   
    insert into @table values ('1@!@#$%^&*34567','12345678/12345678','')   
    insert into @table values ('1234567890','12345678','12345678')
    --------------------------------------
    select 
    Number1,
    dbo.getOnlyNumber(Number1) OPNumber1,
    Number2,
    dbo.getOnlyNumber(Number2) OPNumber2,
    Number3,
    dbo.getOnlyNumber(Number3) OPNumber3
    from @table
    ---------------------------------------
    alter function getOnlyNumber(@number varchar(30))
    returns varchar(30)
    as
    begin
    --	declare @number varchar(30)
    --	set @number = '12345678/12345678'
    	declare @return varchar(30),@replace1 varchar(30)
    	set @replace1 = replace(@number,'/','')
    	if len(@number) = len(@replace1)	-- does not contain slash character
    	begin
    		set @return = rtrim(ltrim(dbo.[AlphaRemove](@number)))
    		if len(@return) = 8 set @return = @return else set @return = null
    	end
    	else								-- contain slash character
    	begin
    		declare @counter int, @replace2 varchar(30)
    		set @counter = 1
    		set @replace2 = replace(@number,'/','.')
    		while @counter <= (len(@number) - len(@replace1)) + 1
    		begin
    			set @return = ''
    			if len(parsename(@replace2,@counter)) = 8
    				set @return = @return + '/' + parsename(@replace2,@counter)
    			set @counter = @counter + 1
    		end
    		if len(@return) <> 0 set @return = right(@return,len(@return)-1)
    	end
    	
    	return @return
    end
    ------------------------
    create function [dbo].[AlphaRemove](@String_in varchar(max))
    returns varchar(max)
    as
    begin
    /*
    	*****	Takes a string variable and turns it into a set of 
    	*****	numbers separated by spaces.
    	*****	Despite the name, it also removes punctuation, not
    	*****	just letters.
    	*****	Input string must be simple ASCII, not Unicode.
    	*****	(No accented letters, etc.)
    */
    	declare @sub char(1)
    --Letters
    	while patindex('%[a-z]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[a-z]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    --Punctuation
    	while patindex('%[!-)]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    	while patindex('%[+-/]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    	while patindex('%[:-=]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while patindex('%[?-@]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	set @string_in = replace(@string_in, '[', '')
    	while patindex('%[\-`]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[\-`]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while patindex('%[{-~]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[{-~]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while charindex('  ', @string_in, 0) > 0
    		set @string_in = replace(@string_in, '  ', ' ')
    	return @string_in
    end


    Regards, RSingh

    Saturday, October 19, 2013 2:07 PM

All replies

  • Can you please post the data format in a clear tabular form ? It is hard to read.

    Regards, RSingh


    • Edited by RSingh() Saturday, October 19, 2013 5:02 AM
    Saturday, October 19, 2013 5:02 AM
  • Number1 Number2 Number3
    12345678 56 NULL
    12345678/12345678 qewq21321314  
    M 12345678 12345678 12345678
    N 12345678 1234567890/1234567890  
    1@!@#$%^&*34567 12345678/12345678  
    1234567890 12345678 12345678

    Number1 OPNum1 Number2 OPNum2 Number3 OPNum3
    12345678 12345678 56 null NULL null
    12345678/12345678 12345678/12345678 qewq21321314 21321314   null
    M 12345678 12345678 12345678 12345678 12345678 12345678
    N 12345678 12345678 1234567890/1234567890 null null
    1@!@#$%^&*34567 Null 12345678/123456789 12345678 null
    1234567890 null 12345678 12345678 12345678 12345678
     
    Saturday, October 19, 2013 7:42 AM
  • Refer the below solution,

    declare @table table(Number1 varchar(30), Number2 varchar(30), Number3 varchar(30) )
    insert into @table values ('12345678','56',NULL)
    insert into @table values ('12345678/12345678','qewq21321314','')   
    insert into @table values ('M 12345678','12345678','12345678') 
    insert into @table values ('N 12345678','1234567890/1234567890','')   
    insert into @table values ('1@!@#$%^&*34567','12345678/12345678','')   
    insert into @table values ('1234567890','12345678','12345678')
    --------------------------------------
    select 
    Number1,
    dbo.getOnlyNumber(Number1) OPNumber1,
    Number2,
    dbo.getOnlyNumber(Number2) OPNumber2,
    Number3,
    dbo.getOnlyNumber(Number3) OPNumber3
    from @table
    ---------------------------------------
    alter function getOnlyNumber(@number varchar(30))
    returns varchar(30)
    as
    begin
    --	declare @number varchar(30)
    --	set @number = '12345678/12345678'
    	declare @return varchar(30),@replace1 varchar(30)
    	set @replace1 = replace(@number,'/','')
    	if len(@number) = len(@replace1)	-- does not contain slash character
    	begin
    		set @return = rtrim(ltrim(dbo.[AlphaRemove](@number)))
    		if len(@return) = 8 set @return = @return else set @return = null
    	end
    	else								-- contain slash character
    	begin
    		declare @counter int, @replace2 varchar(30)
    		set @counter = 1
    		set @replace2 = replace(@number,'/','.')
    		while @counter <= (len(@number) - len(@replace1)) + 1
    		begin
    			set @return = ''
    			if len(parsename(@replace2,@counter)) = 8
    				set @return = @return + '/' + parsename(@replace2,@counter)
    			set @counter = @counter + 1
    		end
    		if len(@return) <> 0 set @return = right(@return,len(@return)-1)
    	end
    	
    	return @return
    end
    ------------------------
    create function [dbo].[AlphaRemove](@String_in varchar(max))
    returns varchar(max)
    as
    begin
    /*
    	*****	Takes a string variable and turns it into a set of 
    	*****	numbers separated by spaces.
    	*****	Despite the name, it also removes punctuation, not
    	*****	just letters.
    	*****	Input string must be simple ASCII, not Unicode.
    	*****	(No accented letters, etc.)
    */
    	declare @sub char(1)
    --Letters
    	while patindex('%[a-z]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[a-z]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    --Punctuation
    	while patindex('%[!-)]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    	while patindex('%[+-/]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[!-/]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	
    	while patindex('%[:-=]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while patindex('%[?-@]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[:-@]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	set @string_in = replace(@string_in, '[', '')
    	while patindex('%[\-`]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[\-`]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while patindex('%[{-~]%', @string_in) > 0
    	begin
    		set @sub = substring(@string_in, patindex('%[{-~]%', @string_in), 1)
    		
    		set @string_in = replace(@string_in, @sub, '')
    	end
    	while charindex('  ', @string_in, 0) > 0
    		set @string_in = replace(@string_in, '  ', ' ')
    	return @string_in
    end


    Regards, RSingh

    Saturday, October 19, 2013 2:07 PM