如何编写SQL Server查询以从下面的字符串中获取所需的结果？

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?

如何从上面的字符串值轻松获得结果94941?

2 个解决方案

#1

You can use Json function

你可以使用Json功能

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

Here is a second idea: (Quick and Dirty)

这是第二个想法:(快速和肮脏)

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

#2

Based on the question the answer is

根据问题答案是

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

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

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

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

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

这完全取决于@code中的数据。

#1