Post reply

access token value

  • January 28, 2019 at 5:09 am

    #409465

    Hi,

    i have the below script :
    {
      "access_token": "7mYWLVjfbttKjxT4_T_rL8SGFMKHjjjhhiTTkklkhKHKKHKKxatwAUi2BaD5e6uJcRPdP9Gf261RnwUZQf4X7jfAfh8BgoQ7pHsj4z9VqlaZ3FdI8t7TZs0P-txg0JU-zJg",
      "token_type": "bearer",
      "expires_in": 3600
    }

    and i need to extract access_token value
    7mYWLVjfbttKjxT4_T_rL8SGFMKHjjjhhiTTkklkhKHKKHKKxatwAUi2BaD5e6uJcRPdP9Gf261RnwUZQf4X7jfAfh8BgoQ7pHsj4z9VqlaZ3FdI8t7TZs0P-txg0JU-zJg

    i tried the below but it is not return correct

    declare @string varchar(8000) = '{"access_token": "7mYWLVjfbttKjxT4_T_rL8SKKrGgIpG0EjT6pKYUC_BvINYnKITNA3EZ1dXaxU6QPSaxatwAUi2BaD5e6uJcRPdP9Gf261RnwUZQf4X7jfAfh8BgoQ7pHsj4z9VqlaZ3FdI8t7TZs0P-txg0JU-zJg","token_type": "bearer","expires_in": 3600}'
    select substring(@string, charindex('"', @string) + 17, charindex(' "', @string) - charindex('"', @string) - 1)

  • January 28, 2019 at 7:11 am

    #2020276

    I assume you are using SQL Server 2014; as that's the forum you have posted in. If you are, instead, using SQL server 2016+ then you have access to OPENJSON.

    For example:
    DECLARE @JSON nvarchar(MAX) =
    '{
    "access_token": "7mYWLVjfbttKjxT4_T_rL8SGFMKHjjjhhiTTkklkhKHKKHKKxatwAUi2BaD5e6uJcRPdP9Gf261RnwUZQf4X7jfAfh8BgoQ7pHsj4z9VqlaZ3FdI8t7TZs0P-txg0JU-zJg",
    "token_type": "bearer",
    "expires_in": 3600
    }';

    SELECT [value] AS access_token
    FROM OPENJSON(@JSON)
    WHERE = 'access_token';

    SELECT access_token
    FROM OPENJSON(@JSON)
    WITH (access_token varchar(255) '$.access_token',
          token_type varchar(15) '$.token_type',
          expires_in int '$.expires_in');

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • January 28, 2019 at 7:35 am

    #2020280

    We are using sql server 2014, how can i do it

  • January 28, 2019 at 7:57 am

    #2020284

    This'll work for access_token, provided that the access token is always quoted:

    SELECT *, SUBSTRING(@JSON,NULLIF(A.S,0)+1, NULLIF(T.E,0)-(A.S+1))
    FROM (VALUES(CHARINDEX('"' + @key + '"', @JSON))) K(CI)
        CROSS APPLY (VALUES(CHARINDEX('"', @JSON,K.CI + LEN('"' + @key + '"')))) A(S)
        CROSS APPLY (VALUES(CHARINDEX('"', @JSON,A.S+1))) T(E);

    If, however, you need to  query the JSON more freely than that you'll need to use a JSON parser. There are some T-SQL solutions out there (if you search), but they aren't always perfect. Otherwise you could use a CLI solution. If neither of those are options, that only leaves you with the option of upgrading though.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • January 29, 2019 at 2:18 am

    #2020375

    Thank yo So Much,  working fine

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply