-4

I would like to retrieve the value inside a Text. Here is my example:

<Host_In_Params>{"Zip_Code":"94941"}</Host_In_Params>

How can I easily get the result 94941 from the above string value?

halfer
  • 19,824
  • 17
  • 99
  • 186
Sambit Sahoo
  • 1
  • 5
  • First you need to show us what you have tried and where you got stuck. This isnt'a `code-for-free` service. Please read [how to ask a question on Stackoverflow](https://stackoverflow.com/help/how-to-ask) – Radu Gheorghiu Mar 02 '18 at 07:50
  • 1
    Use [string functions](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql) – Ocaso Protal Mar 02 '18 at 07:51
  • 1
    Maybe solution on this can help you https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string – user6860809 Mar 02 '18 at 07:53

2 Answers2

0

You can use Json function

SELECT 
     '{"Zip_Code":"94941"}'
    ,JSON_VALUE('{"Zip_Code":"94941"}', '$.Zip_Code') AS YourValue

enter image description here

Here is a second idea: (Quick and Dirty)

enter image description here

DECLARE @test varchar(100);  
SELECT @test = '<Host_In_Params>{"Zip_Code":"94941"}</Host_In_Params>';  
SELECT SUBSTRING(
             @test
            ,CHARINDEX('Zip_Code', @test) + 11
            ,5
);
Arnaud Gastelblum
  • 312
  • 1
  • 11
0

Based on the question the answer is 

DECLARE @CODE VARCHAR(50)
SET @CODE = '{"Zip_Code":"94941"}'
SELECT @CODE

SELECT REVERSE(SUBSTRING (REVERSE(@CODE), CHARINDEX('"', @CODE) +1 , 5))

This completely varies based on the data in the @code.

vahdet
  • 6,357
  • 9
  • 51
  • 106
user9192401
  • 168
  • 8