CODEWITHSUNDEEP
sqlapache-spark-sql
David
David

Reputation: 459

Workaround for using BETWEEN and a WILDCARD

I have a fairly large table which contains ICD10 codes (icd10_table). I want to pull a bunch of different ICD10 codes (code) based on ranges. In short, there are parent ICD10 codes that have "children" that can be pretty expansive. For instance: ICD10 'M04' can include 'M041', 'M045', 'M04679', 'M04000', 'M04999' and so forth. There really isn't a pattern here and it's very time-consuming to look up and figure out every single combination. Under normal circumstances a simple query with a wildcard like this would pull everything I need:

SELECT *
FROM icd10_table
WHERE code LIKE 'M04%'

However, I'm trying to pull ranges of specific ICD10 codes and was trying to do something like this: 

SELECT *
FROM icd10_table
WHERE code BETWEEN 'M00' AND 'M02%'
OR code BETWEEN 'M04' AND 'M04%'
OR code BETWEEN 'M05' AND 'M14%'
OR code BETWEEN 'M15' AND 'M19%'

But this just cuts off the child ICD10's and doesn't actually apply the wildcard. What is a good workaround for using BETWEEN and a wildcard to pull every parent/child ICD10 in range?

Upvotes: 0

Views: 352

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Try something like this:

SELECT *
FROM icd10_table
WHERE code >= 'M00' AND code < 'M02' OR
      code >= 'M04' AND code < 'M05' OR
      code >= 'M05' AND code < 'M15' OR
      code >= 'M15' AND code < 'M20'

Your formulation has '%' whose ASCII value is less than any digit, so it effectively cuts off the comparison.

Upvotes: 1

Related Questions