我正在使用以下方法搜索 SQL Server，并且我想找到一種方法來減少要搜索的郵政編碼范圍的查詢大小：
SELECT TOP (100) *
FROM XXXX (NOLOCK)
WHERE (Request like '%<BillCountry>US</BillCountry>%')
AND (Request like '%<BillPostal>83%' OR Request like '%<BillPostal>84%' OR Request like '%<BillPostal>85%' OR Request like '%<BillPostal>86%' OR Request like '%<BillPostal>87%' OR Request like '%<BillPostal>91%' OR Request like '%<BillPostal>92%' OR Request like '%<BillPostal>93%' OR Request like '%<BillPostal>94%')
AND (CreatedUTC between '2022-02-01' and '2022-03-01')
ORDER BY CreatedUTC DESC
它<BillPostal>XXXXX</BillPostal>位于已保存的 XML 回應的深處。我正在搜索一系列 BillPostal，例如 83XXX-87XXX 和 91XXX-94XXX。也許這是唯一的方法？
uj5u.com熱心網友回復：
在 Sql Server 中，您可以[]在 LIKE/PATINDEX 的模式語法中使用字符類。
所以 Request 的標準可以用高爾夫球編碼
SELECT TOP (100) *
FROM XXXX
WHERE (Request like '%<BillCountry>US</BillCountry>%')
AND (Request like '%<BillPostal>8[3-7]%'
OR Request like '%<BillPostal>9[1-4]%')
AND (CreatedUTC between '2022-02-01' and '2022-03-01')
ORDER BY CreatedUTC DESC;
uj5u.com熱心網友回復：
您可以將大部分條件卸載到存在檢查中，并在單獨的表中使用查找項。一個例子是：
with lookups as (
select '<BillPostal>83' term union all
select '<BillPostal>84' union all
select '<BillPostal>85'
), testdata as (
select '<xml><element><BillPostal>85</billpostal></element></xml>' col union all
select '<xml><element><BillPostal>81</billpostal></element></xml>' union all
select '<xml><element><BillPostal>86</billpostal></element></xml>' union all
select '<xml><element><BillPostal>84</billpostal></element></xml>'
)
select *
from testdata
where exists (select * from lookups where CharIndex(term,col) > 0);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/435487.html