我可以按状态从MS SQL Server的地址列中筛选行吗？ [英] Can I filter rows by state from address column in MS SQL Server?

问题描述

CREATE TABLE Customer(
id INT PRIMARY KEY,
Name VARCHAR(128) NOT NULL,
Email VARCHAR(128) NOT NULL,
customer_id VARCHAR(16) NOT NULL,
address NVARCHAR(max),
created_time DateTimeOffset NOT NULL default SYSDATETIMEOFFSET()
) 

CREATE UNIQUE INDEX idx_customer_email
ON Customer(customer_id, Email)

INSERT INTO Customer(id, Name, Email, customer_id, address)
VALUES(0,'a','[e-mail]','a000', '"house_number": 10, "building_name": "B0", "street": "street0",
   "city": "Guwahati", "state": "Assam", "country" : "India", "Zipcode" : 781017'),
  (1,'b','[e-mail]','b001', '"house_number": 11, "building_name": "B1", "street": "street1",
   "city": "Patna", "state": "Bihar", "country" : "India", "Zipcode" : 801101'),
  (2,'c','[e-mail]','c002', '"house_number": 12, "building_name": "B2", "street": "street2",
   "city": "Raipur", "state": "Chhattisgarh", "country" : "India", "Zipcode" : 493661'),
  (3,'d','[e-mail]','d003', NULL),
  (4,'e','[e-mail]','e004', '"house_number": 14, "building_name": "B4", "street": "street4",
   "city": "Rajkot", "state": "Gujrat", "country" : "India", "Zipcode" : 360110'),
  (5,'f','[e-mail]','f005', '"house_number": 15, "building_name": "B5", "street": "street5",
   "city": "Patipat", "state": "Haryana", "country" : "India", "Zipcode" : 132113'),
  (6,'g','[e-mail]','g006', '"house_number": 16, "building_name": "B6", "street": "street6",
   "city": "Bangalore", "state": "Karnataka", "country" : "India", "Zipcode" : 560101'),
  (7,'h','[e-mail]','h007', '"house_number": 17, "building_name": "B7", "street": "street7",
   "city": "Nagpur", "state": "Maharashtra", "country" : "India", "Zipcode" : 440001'),
  (8,'i','[e-mail]','i008', NULL),
  (9,'j','[e-mail]','j009', '"house_number": 19, "building_name": "B9", "street": "street9",
   "city": "Amritsar", "state": "Punjab", "country" : "India", "Zipcode" : 143101')

上面的查询我得到下表。

After running the above query I get the following table.

现在，我只想从地址列中查询状态名称，而无需创建另一个地址表。预期的结果将是：
阿萨姆邦
比哈尔邦
恰蒂斯加尔邦
古杰拉特
哈里亚纳邦
卡纳塔克邦
马哈拉施特拉邦

Now I want to query just the name of states from address column without creating another address table. The expected result will be : Assam Bihar Chhattisgarh Gujrat Haryana Karnataka Maharashtra

能否获得以下结果？
如果不提供其他选择。

Can the following results be obtained ? If not kindly provide the alternative.

推荐答案

选项1：

您的意思是整个地址行为NULL吗？

Do you mean the whole address line is NULL?

SELECT  *
FROM    dbo.Customer
WHERE   [address] IS NOT NULL;

选项2：

通配符在文本中搜索。假设一个空状态是状态：。对于大量数据，此方法的效率也很低：

Wildcard searches in the text. Assuming that an empty state is "state": "". This method would also be quite inefficient for large volumes of data:

SELECT  * 
FROM    dbo.Customer
WHERE   ISNULL([address], '') LIKE '%"state": ""%';

选项3：

使用JSON获取地址字段中的字段。我使用 REPLACE 是因为我不确定您的是通过复制粘贴完成的，还是实际上是以这种方式存储的。如果是复制粘贴问题，则可以删除：

Using JSON to get the fields within the Address field. I have used REPLACE as I wasn't sure if your "" was done via copy and paste or whether they were actually stored that way. You can remove if it was a copy paste issue:

WITH AddressJSON AS
(
SELECT  c.*
,       IIF(c.[address] IS NULL, NULL,  '{ "address": { ' + REPLACE(REPLACE(c.[address], '"', '"'), '"', '"') + ' } }') AS AddressQuotes
FROM    dbo.Customer c
)
SELECT  aj.*
,       JSON_VALUE(aj.AddressQuotes, '$.address.house_number')      AS HouseNumber
,       JSON_VALUE(aj.AddressQuotes, '$.address.building_name')     AS BuildingName
,       JSON_VALUE(aj.AddressQuotes, '$.address.street')            AS Street
,       JSON_VALUE(aj.AddressQuotes, '$.address.city')              AS City
,       JSON_VALUE(aj.AddressQuotes, '$.address.state')             AS [State]
,       JSON_VALUE(aj.AddressQuotes, '$.address.country')           AS Country
,       JSON_VALUE(aj.AddressQuotes, '$.address.Zipcode')           AS Zipcode
FROM    AddressJSON aj
WHERE   JSON_VALUE(aj.AddressQuotes, '$.address.state') IS NOT NULL;

最后一个查询产生：

The last query yields:

选项4：

这是基于您修改的问题：

This is based on your amended question:

WITH AddressJSON AS
(
SELECT  c.*
,       IIF(c.[address] IS NULL, NULL,  '{ "address": { ' + REPLACE(REPLACE(c.[address], '"', '"'), '"', '"') + ' } }') AS AddressQuotes
FROM    dbo.Customer c
)
SELECT      TRIM(STRING_AGG(JSON_VALUE(AddressQuotes, '$.address.state'), ' ' )) AS AddState
FROM        AddressJSON
WHERE       JSON_VALUE(AddressQuotes, '$.address.state') IS NOT NULL;

产生的结果是：

这篇关于我可以按状态从MS SQL Server的地址列中筛选行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！