Searching a range when using like %-CodePudding

I'm searching SQL Server using the following and I want to find a way to reduce the query size when it comes to the range of postal codes being searched:

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

The <BillPostal>XXXXX</BillPostal> is deep inside a saved XML response. I'm searching for a range of BillPostal such as 83XXX-87XXX and 91XXX-94XXX. Maybe this is the only way?

CodePudding user response：

In Sql Server you can use a character class [] in the pattern syntax for LIKE/PATINDEX.

So the criteria for Request can be golfcoded

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;

CodePudding user response：

You could offload the bulk of your criteria to an exists check with the lookup terms in a separate table. An example would be:

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);