locked
Extracting a number from a column RRS feed

  • Question

  • User140340533 posted

    Hi, 

    I have a column containing text but after the word 'ScanCode' there is a number that I need to extract. 

    The key to check for the number is ScanCode

    Example:  

    Model1, shipping A with ScanCode 21443.---------------->Number to Extract = 21443

    Model25, Order 25B with ScanCode. 22444523 no need to reship---------------->Number to Extract = 22444523

    Can this be done using a SQL Query ? 

    Thanks

    Monday, September 11, 2017 7:35 PM

Answers

  • User2103319870 posted

    Example : 'on 8/15/2008, the scan code was '123223'

    You could create Scalar Function in SQLServer which will extract numbers based on the lookstring we provides.

    Sample Function Code

    CREATE FUNCTION dbo.fnExtractNumber
    (	
    	@inputstring Varchar(Max),
    	@LookupVariable Varchar(Max)
    )
    RETURNS Varchar(MAX) 
    AS
    BEGIN
    		
    			-- Add a character at the to make search of the numeric string end work
    			SELECT @inputstring += @inputstring  + ' '
    			-- Find String token and save the rest of the string to the variable
    			SELECT @inputstring = SUBSTRING(@inputstring, PATINDEX('%' + @LookupVariable + '%', @inputstring) + LEN(@LookupVariable), 10000) 
    			-- The extract string from first numeric character unitl last numeric 
    			SELECT @inputstring = SUBSTRING(@inputstring, PATINDEX('%[0-9]%', @inputstring), PATINDEX('%[0-9][a-z !@#$%^&*(()_]%', @inputstring) - PATINDEX('%[0-9]%', @inputstring) + 1)
    				RETURN @inputstring 
    END
    

    Now you can use below query to fetch numbers

    SELECT dbo.fnExtractNumber(Column1,'scan code') from #Temp

    Source URL

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 15, 2017 5:36 PM

All replies

  • User2103319870 posted

    xample:  

    Model1, shipping A with ScanCode 21443.---------------->Number to Extract = 21443

    Model25, Order B with ScanCode. 22444523 no need to reship---------------->Number to Extract = 22444523

    Can this be done using a SQL Query ? 

    You can try with the below query

    SELECT SUBSTRING(ColumnName, PATINDEX('%[0-9]%', ColumnName), LEN(ColumnName))  FROM YourTable
    Monday, September 11, 2017 8:42 PM
  • User364663285 posted

    Hi, 

    I have a column containing text but after the word 'ScanCode' there is a number that I need to extract. 

    The key to check for the number is ScanCode

    Example:  

    Model1, shipping A with ScanCode 21443.---------------->Number to Extract = 21443

    Model25, Order 25B with ScanCode. 22444523 no need to reship---------------->Number to Extract = 22444523

    Can this be done using a SQL Query ? 

    Thanks

    You can use Cursor to extract such column.

    To that column, try to detect the numeric value right after "ScanCode".

    And also to detect the end of such numeric ScanCode, and to extract it.

    Wednesday, September 13, 2017 1:18 PM
  • User140340533 posted

    Yes but how ?

    Wednesday, September 13, 2017 6:44 PM
  • User452040443 posted

    Hi,

    Try something like this:

    declare @MyTable Table (MyColumn varchar(200));
    
    insert into @MyTable values
    ('Model1, shipping A with ScanCode 21443.'),
    ('Model25, Order 25B with ScanCode. 22444523 no need to reship');
    
    select
        LEFT(sc.P1, PATINDEX('%[^0-9]%', sc.P1) - 1)
    from @MyTable
    cross apply
    (
        select 
            LTRIM(RIGHT(MyColumn, LEN(MyColumn) - PATINDEX('%ScanCode%', MyColumn) - 8)) as P1
    ) as sc

    Hope this help

    Wednesday, September 13, 2017 7:39 PM
  • User347430248 posted

    Hi victor2,

    I think that the suggestion given by the "A2H" is according to your requirement and can able to solve your issue.

    did you try to test it on your side?

    if no, I suggest you to make a test with it.

    if you think that it can solve your issue then I suggest you to mark his suggestion as an answer.

    if you are having some trouble to implement that suggestion then let us know about that.

    we will try to provide further suggestion to solve it.

    Regards

    Deepak

    Thursday, September 14, 2017 3:21 AM
  • User140340533 posted

    That's not working Deepak, 

    Example : 'on 8/15/2008, the scan code was '123223'

    Thursday, September 14, 2017 10:38 PM
  • User347430248 posted

    Hi victor2,

    I try to create a sample table with dummy data looks like below.

    Query:

    SELECT SUBSTRING(col1, CHARINDEX('ScanCode',col1) + LEN('ScanCode'), LEN(col1)) FROM s_data

    Output:

    you can further modify it as per your requirement.

    Regards

    Deepak

    Friday, September 15, 2017 9:00 AM
  • User2103319870 posted

    Example : 'on 8/15/2008, the scan code was '123223'

    You could create Scalar Function in SQLServer which will extract numbers based on the lookstring we provides.

    Sample Function Code

    CREATE FUNCTION dbo.fnExtractNumber
    (	
    	@inputstring Varchar(Max),
    	@LookupVariable Varchar(Max)
    )
    RETURNS Varchar(MAX) 
    AS
    BEGIN
    		
    			-- Add a character at the to make search of the numeric string end work
    			SELECT @inputstring += @inputstring  + ' '
    			-- Find String token and save the rest of the string to the variable
    			SELECT @inputstring = SUBSTRING(@inputstring, PATINDEX('%' + @LookupVariable + '%', @inputstring) + LEN(@LookupVariable), 10000) 
    			-- The extract string from first numeric character unitl last numeric 
    			SELECT @inputstring = SUBSTRING(@inputstring, PATINDEX('%[0-9]%', @inputstring), PATINDEX('%[0-9][a-z !@#$%^&*(()_]%', @inputstring) - PATINDEX('%[0-9]%', @inputstring) + 1)
    				RETURN @inputstring 
    END
    

    Now you can use below query to fetch numbers

    SELECT dbo.fnExtractNumber(Column1,'scan code') from #Temp

    Source URL

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 15, 2017 5:36 PM