Extract a specific value from a string in a column

Need help with a situation, I need to extract a specific value from a column containing string. The string varies over different records, but the value I need to extract is always preceded by the word “VERSION”

I tried to user REVERSE & SUBSTRING but not able to get exact results. Any help will be greatly appreciated

the table looks like this

Here’s my attempt to solve the problem

SELECT DISTINCT A.USER_ID,USER_AGNT_STR,
  REVERSE(
        SUBSTRING(
            REVERSE(SUBSTRING(USER_AGNT_STR,20,PATINDEX('%VERSION%',USER_AGNT_STR))),
            4,
            PATINDEX('%.%',REVERSE(SUBSTRING(USER_AGNT_STR,5,PATINDEX('%VERSION%',USER_AGNT_STR)))))
    ) AS APP_VERSION
FROM TABLE A
WHERE USER_AGNT_STR LIKE '%VERSION%'
GROUP BY A.USER_ID,A.USER_AGNT_STR

And I want my results to look like

USER ID       APP_VERSION
1             2.3.1
2             2.3
5             2.1
6             2.1
7             2.3.1
9             2.3
10            2.3

Answer

Here is one way to do it.

This assumes that version is always preceeded by a space, then followed by a space, then by the version number, then by a comma.

The following expression gives you the starting position of the version number (9 is just the length of string version plus the surrounding spaces):

CHARINDEX(' version ', user_agnt_str) + 9

Then, you can compute the position of the next comma after that position, which signals the end of the version number:

CHARINDEX(',', user_agnt_str, CHARINDEX(' version ', user_agnt_str) + 9)

Both information can now be used with SUBSTRING:

SUBSTRING(
    user_agnt_str, 
    CHARINDEX(' version ', user_agnt_str) + 9,
    CHARINDEX(',', user_agnt_str, CHARINDEX(' version ', user_agnt_str) + 9) 
        - CHARINDEX(' version ', user_agnt_str) - 9
)

Demo on DB Fiddle:

WITH t AS (
  SELECT '{''HTTP_USER_AGENT'':''MOBILE/4.2.95, version 2.3.1, build 95 (iPhone 65;Apple ...)''}' user_agnt_str
)
SELECT 
    user_agnt_str,
    SUBSTRING(
        user_agnt_str, 
        CHARINDEX(' version ', user_agnt_str) + 9,
        CHARINDEX(',', user_agnt_str, CHARINDEX(' version ', user_agnt_str) + 9) 
            - CHARINDEX(' version ', user_agnt_str) - 9
    ) version
FROM t
GO

user_agnt_str                                                                    | version
:------------------------------------------------------------------------------- | :------
{'HTTP_USER_AGENT':'MOBILE/4.2.95 version 2.3.1 build 95 (iPhone 65;Apple ...)'} | 2.3.1