Searching a range when using like %

February 28, 2022

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?

>Solution :

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;