locked
get 10 digit number from string RRS feed

  • Question

  • Hi all

    I have one string I need to get 10 digit number from it .

    1.abnh-US-HoP--String-123770752-21-Mar-13

    2.sdfn-fdsfnter-1234567890

    I require only

    1.123770752

    2.1234567890

    Please help.

    Thursday, May 30, 2013 8:33 AM

Answers

  • Declare @vTable Table (id int, val varchar(100))
    Insert into @vTable 
    	Select 1,'abnh-US-HoP-String-123770752-21-Mar-13' Union all
    	Select 2,'sdfn-fdsfnter-1234567890'
    
    
    Select id,value
    from @vTable
    Cross apply dbo.fnSplit(val,'-') b
    where ISNUMERIC(value)= 1
    and LEN(value)>=9

    here is the code of dbo.fnSplit function.

    CREATE FUNCTION [dbo].[fnSplit]
    (@pString varchar(max),@pSplitChar char(1))
    returns @tblTemp table (tid int,value varchar(1000))
    as
    begin
    
    	declare @vStartPosition		int
    	declare @vSplitPosition		int
    	declare @vSplitValue		varchar(1000)
    	declare @vCounter		int
    	set @vCounter=1
    
    	select @vStartPosition = 1,@vSplitPosition=0
    
    	set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	if (@vSplitPosition=0 and len(@pString) != 0)
    	begin
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			1	,
    			@pString		
    			)
    		return		--------------------------------------------------------------->>
    	end
    	set @pString=@pString+@pSplitChar
    	while (@vSplitPosition > 0 )
    	begin
    		set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
    		set @vSplitValue = ltrim(rtrim(@vSplitValue))
    
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			@vCounter	,
    			@vSplitValue		
    			)
    		set @vCounter=@vCounter+1
    		set @vStartPosition = @vSplitPosition + 1
    		set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	end
    
    	return
    end
    
    
    GO
    
    
    


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Thursday, May 30, 2013 9:05 AM
  • Try this one,

    declare @temp table(strs nvarchar(50))
    insert into @temp values('1.abnh-US-HoP--String-123770752-21-Mar-13')
    insert into @temp values('2.sdfn-fdsfnter-1234567890')
    ------------------------- FINAL QUERY
    SELECT PREFIX + '.' + 
    	CASE WHEN CHARINDEX('-',SUFFIX) > 0 THEN LEFT(SUFFIX,CHARINDEX('-',SUFFIX)-1) 
    		ELSE SUFFIX 
    	END AS SUFFIX FROM (
    	SELECT PREFIX , RIGHT(SUFFIX,LEN(SUFFIX)+1-PATINDEX('%[0-9]%',SUFFIX)) AS SUFFIX FROM (
    		SELECT PARSENAME(REPLACE(RTRIM(strs),' ','.'), 2) AS PREFIX,PARSENAME(REPLACE(RTRIM(strs),' ','.'), 1) AS SUFFIX FROM @temp
    	) X ) Y


    Regards, RSingh

    Thursday, May 30, 2013 9:07 AM

All replies

  • SELECT SUBSTRING([COLUMN], PATINDEX('%[0-9]%', [COLUMN]), LEN([COLUMN])) AS NUMBER FROM [TABLE]
    Thursday, May 30, 2013 8:38 AM
  • Try this,

    declare @test table (c1  varchar(100))
    insert into @test values ('abnh-US-HoP--String-123770752-21-Mar-13'),('sdfn-fdsfnter-1234567890')
    
    
    select case when  CHARINDEX('-',STUFF(C1,1,PATINDEX('%[0-9]%',C1),'') ) >0 then  
    STUFF(STUFF(C1,1,PATINDEX('%[0-9]%',C1),''),CHARINDEX('-',STUFF(C1,1,PATINDEX('%[0-9]%',C1),'') ),len(c1),'')
    else STUFF(C1,1,PATINDEX('%[0-9]%',C1),'') end
     from @test


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, May 30, 2013 9:01 AM
  • Declare @vTable Table (id int, val varchar(100))
    Insert into @vTable 
    	Select 1,'abnh-US-HoP-String-123770752-21-Mar-13' Union all
    	Select 2,'sdfn-fdsfnter-1234567890'
    
    
    Select id,value
    from @vTable
    Cross apply dbo.fnSplit(val,'-') b
    where ISNUMERIC(value)= 1
    and LEN(value)>=9

    here is the code of dbo.fnSplit function.

    CREATE FUNCTION [dbo].[fnSplit]
    (@pString varchar(max),@pSplitChar char(1))
    returns @tblTemp table (tid int,value varchar(1000))
    as
    begin
    
    	declare @vStartPosition		int
    	declare @vSplitPosition		int
    	declare @vSplitValue		varchar(1000)
    	declare @vCounter		int
    	set @vCounter=1
    
    	select @vStartPosition = 1,@vSplitPosition=0
    
    	set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	if (@vSplitPosition=0 and len(@pString) != 0)
    	begin
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			1	,
    			@pString		
    			)
    		return		--------------------------------------------------------------->>
    	end
    	set @pString=@pString+@pSplitChar
    	while (@vSplitPosition > 0 )
    	begin
    		set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
    		set @vSplitValue = ltrim(rtrim(@vSplitValue))
    
    		INSERT INTO @tblTemp
    			(
    			tid		,
    			value	
    			)
    		VALUES
    			(
    			@vCounter	,
    			@vSplitValue		
    			)
    		set @vCounter=@vCounter+1
    		set @vStartPosition = @vSplitPosition + 1
    		set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
    	end
    
    	return
    end
    
    
    GO
    
    
    


    Please visit my Blog for some easy and often used t-sql scripts
    My BizCard

    Thursday, May 30, 2013 9:05 AM
  • Try this one,

    declare @temp table(strs nvarchar(50))
    insert into @temp values('1.abnh-US-HoP--String-123770752-21-Mar-13')
    insert into @temp values('2.sdfn-fdsfnter-1234567890')
    ------------------------- FINAL QUERY
    SELECT PREFIX + '.' + 
    	CASE WHEN CHARINDEX('-',SUFFIX) > 0 THEN LEFT(SUFFIX,CHARINDEX('-',SUFFIX)-1) 
    		ELSE SUFFIX 
    	END AS SUFFIX FROM (
    	SELECT PREFIX , RIGHT(SUFFIX,LEN(SUFFIX)+1-PATINDEX('%[0-9]%',SUFFIX)) AS SUFFIX FROM (
    		SELECT PARSENAME(REPLACE(RTRIM(strs),' ','.'), 2) AS PREFIX,PARSENAME(REPLACE(RTRIM(strs),' ','.'), 1) AS SUFFIX FROM @temp
    	) X ) Y


    Regards, RSingh

    Thursday, May 30, 2013 9:07 AM
  • Hi,

    Try this..

    DECLARE @table TABLE(testcol varchar(50))
    
    INSERT INTO @table 
            ( testcol )
    VALUES  ( 'abnh-US-HoP-String-123770752-21-Mar-13'  -- testcol - varchar(50)
              ),
    ('sdfn-fdsfnter-1234567890')          
    
      
     SELECT SUBSTRING(testcol, PATINDEX('%[0-9]%', testcol), LEN(testcol) - CHARINDEX('-',testcol,PATINDEX('%[0-9]%', testcol))) AS NUMBER FROM @TABLE

    Regards,

    Brindha.

    Thursday, May 30, 2013 9:07 AM
  • Here is another solution.... 

    First I am splitting a string based of '-' delimiter then I am checking individual string is numeric and it's length is greater than 9 ...

    I think it's better to not to make any assumption , where the number may fall in a string...  

    Declare @t table (string nvarchar(500))
    
    insert into @t 
    select 'abnh-US-HoP--String-123770752-21-Mar-13' union all
    select 'sdfn-fdsfnter-1234567890' union all 
    select '1679867890-sdfn-56411--fdsfnter--'  union all 
    select '15-jan-2014-15-16-Kk-98-2229867890-sdfn-56411--fdsfnter--'
    
    
    
    
    select a.string,y.val from @t a 
    cross apply ( select   cast( '<R>'+ replace(string,'-','</R><R>') + '</R>' as xml) as Str_xml ) x
     cross apply (select a.b.value('.','nvarchar(max)')  as val from str_xml.nodes('R') a(b) ) y 
     where len(y.val)>=9 and ISNUMERIC(y.val)=1
    
    
    
    
    

    Thanks,

    Saurabh 


    http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

    Thursday, May 30, 2013 10:10 AM
  • Yet another solution:

    SELECT SUBSTRING(my_column, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',my_column), 10)
    FROM my_table
    


    Gert-Jan

    Thursday, May 30, 2013 7:25 PM
  • The quick answer is some string functions and REPLACE() to get rid of the non-digits. This is a kludge that you will repeat over and over in the DML and destroying any hope of performance. I will let other people give you some guesses, since we have no regular expression for this string. 

    The right answer is that this column is not in First Normal form (1NF) and you need to put each of the concatenated data elements in a proper columns. I also see that you screwed up the date format. The only format allowed in the ANSI/ISO Standard is ISO-8601 (yyyy-mm-dd), so your local English based dialect (Oracle?) is another thing you will be constantly kludging until you repair  this mess. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 30, 2013 7:58 PM
  • ===========================

    SELECT SUBSTRING(COLUMN_NAME
        , PATINDEX('%[0-9]%', COLUMN_NAME)
        , CASE
          WHEN CHARINDEX('-', SUBSTRING(COLUMN_NAME, PATINDEX('%[0-9]%', COLUMN_NAME), LEN(COLUMN_NAME))) > 0
           THEN CHARINDEX('-', SUBSTRING(COLUMN_NAME, PATINDEX('%[0-9]%', COLUMN_NAME), LEN(COLUMN_NAME))) - 1
          WHEN CHARINDEX('-', SUBSTRING(COLUMN_NAME, PATINDEX('%[0-9]%', COLUMN_NAME), LEN(COLUMN_NAME))) = 0
           THEN LEN(SUBSTRING(COLUMN_NAME, PATINDEX('%[0-9]%', COLUMN_NAME), LEN(COLUMN_NAME)))
          END
          )
    FROM TABLE_NAME

    ===========================

    Replace COLUMN_NAME by your column name that holds the string data.
    Let me know if this resolves your problem.

    Best Luck,
    Shenoy

    Friday, May 31, 2013 7:27 AM
  • Try below,

    declare @strvalue1 varchar(100)
    select @strvalue1 = 'abnh-US-HoP--String-123770752-21-Mar-13'
    SELECT SUBSTRING(@strvalue1 
    	, PATINDEX('%[0-9]%', @strvalue1 )
    	, CASE 
    	WHEN CHARINDEX('-', SUBSTRING(@strvalue1 , PATINDEX('%[0-9]%', @strvalue1 ), LEN(@strvalue1 ))) > 0
    		THEN CHARINDEX('-', SUBSTRING(@strvalue1 , PATINDEX('%[0-9]%', @strvalue1 ), LEN(@strvalue1 ))) - 1
    	WHEN CHARINDEX('-', SUBSTRING(@strvalue1 , PATINDEX('%[0-9]%', @strvalue1 ), LEN(@strvalue1 ))) = 0
    		THEN LEN(SUBSTRING(@strvalue1 , PATINDEX('%[0-9]%', @strvalue1 ), LEN(@strvalue1 )))
    	END	)

    Best Luck,
    Shenoy


    • Edited by shenoyp Wednesday, June 5, 2013 6:53 AM
    Friday, May 31, 2013 4:43 PM