locked
How to extract numbers from string RRS feed

  • Question

  • I have a field in sql 

    23

    23 Days

    2 Weeks

    Net 14 Days

    1 week

    I need to get the number from all these lines

    I need

    23

    23

    14 (2 * 7(weeks))

    14 

    7 (1*7(week))

    Also, any record that doesn't have any numbers should output the number 3.

    I would like to do this within a query. Is there a way to do this?


    Debra has a question

    Monday, July 18, 2016 7:26 PM

Answers

  • I notice now that I did not cover this case:

    "Also, any record that doesn't have any numbers should output the number 3."

    ** Try to do it yourself, and if you have issue please say and we will give you this. 

    Guidance to add this requirement:

    If there is no number, then PATINDEX('%[0-9]%',txt) will be 0

    * The first solution above will return error in that case, therefore you can first check if there is a number or not using CASE statement for example.

    ** Using the second solution it is very simple sine if there is no number you get NULL and then you can use ISNULL in order to return 3


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, July 18, 2016 10:50 PM

All replies

  • Hi ,

      Try using the below function to extract the numbers from a string

      https://gallery.technet.microsoft.com/79ace3db-a3f5-40da-bfb1-13b055b334d3


    Best Regards Sorna

    Monday, July 18, 2016 9:48 PM
  • See also

    Extracting numbers with SQL Server


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, July 18, 2016 10:29 PM
  • Good day, 

    The big issue in your case is not with extract the numbers but with the text format that you use and the types of time that you need to use.

    Firstly I would HIGHLY recommend to rethink about your database structure! It seems like your design is VERY problematic. I will try to help you solve your current issue, but this is something that I will never do with one of my client, and I will make them change the database structure and not use a workaround which might lead to other issues.

    As I started, if the format consistent Number+type and only if the number of days is consistent for each type then the solution might be very simpole. But if you need for example to use Month as well, then you do not have the information in order to get the number of days, since it can be 27,28,30, or 31 days. Therefore Assuming that you only have "day" or week" then the solution is simple.

    Here are two solutions. The first one use PATINDEX function in order to find the number, while the second solution use PARSENAME. The second solution will fit the data that you posted in your question but will not fit if your data includes more then 3 words

    Please check if these fit your needs

    ------------------- DDL+DML
    create table T (txt nvarchar(100))
    insert T (txt) values
    ('23'),
    ('23 Days'),
    ('2 Weeks'),
    ('Net 14 Days'),
    ('1 week')
    GO
    
    -- Solution 1
    With MyCTE as (
    	select 
    		txt
    		,RIGHT(txt,LEN(txt) - PATINDEX('%[0-9]%',txt) + 1) as txt2
    		,PATINDEX('%[^0-9]%',
    			RIGHT(txt,LEN(txt) - PATINDEX('%[0-9]%',txt) + 1)
    		) as len1
    	from T
    )
    select txt,
    	Number_Of_Days = 
    	CASE WHEN len1 = 0 then txt2 else SUBSTRING(txt2, 1, len1) end
    	*
    	CASE
    		WHEN LOWER(txt) like '%day%' then 1
    		WHEN LOWER(txt) like '%week%' then 7
    		ELSE 1
    	END
    from MyCTE
    GO
    
    -- solution 2
    With MyCTE as (
    	SELECT 
    		txt
    		,COALESCE(
    			 TRY_CONVERT (int, PARSENAME(REPLACE(txt,' ','.'), 1) )
    			, TRY_CONVERT (int, PARSENAME(REPLACE(txt,' ','.'), 2) )
    			, TRY_CONVERT (int, PARSENAME(REPLACE(txt,' ','.'), 3) )
    			, TRY_CONVERT (int, PARSENAME(REPLACE(txt,' ','.'), 4) )
    		) as Number
    		,TheType = CASE
    			WHEN LOWER(txt) like '%day%' then 1
    			WHEN LOWER(txt) like '%week%' then 7
    			ELSE 1
    		END
    	from T
    )
    select txt, Number_Of_Days = Number * TheType
    from MyCTE


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, July 18, 2016 10:41 PM
  • I notice now that I did not cover this case:

    "Also, any record that doesn't have any numbers should output the number 3."

    ** Try to do it yourself, and if you have issue please say and we will give you this. 

    Guidance to add this requirement:

    If there is no number, then PATINDEX('%[0-9]%',txt) will be 0

    * The first solution above will return error in that case, therefore you can first check if there is a number or not using CASE statement for example.

    ** Using the second solution it is very simple sine if there is no number you get NULL and then you can use ISNULL in order to return 3


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, July 18, 2016 10:50 PM