0

I have to write a query to fetch rows for 100+ diagnosis codes. What can be an optimal SQL query ? Below are the details of what I have:

Table:

Diagnosis_Cd Description
A00 Cholera
A000 Cholera due to Vibrio cholerae 01, biovar cholerae
A001 Cholera due to Vibrio cholerae 01, biovar eltor
....

Lookup Diagnosis Code list has only the first 3 characters. This is how the query was written when the list was small.

select Diagnosis_Cd
  from <table>
 where Diagnosis_Cd like 'A00%'
    or Diagnosis_Cd like 'B00%'
    or Diagnosis_Cd like 'N00%'
    ...

But now the list has more than 200 diagnosis codes to lookup. Below is what I could come with, so far, considering the easiness to construct the WHERE clause.

WITH temp AS 
(select substring(Diagnosis_Cd, 1, 3) as col1
   from <table>)
select col1
  from temp
 where col1 in ('A00', 
                'B00', 
                'N00',
                ...)

Is there a better way to do it ? "regexp" cannot seem to use wildcard. Thanks in advance for any guidance.

Geeths
  • 81
  • 7
  • Not sure if it's in every RDMBS, but at least some of them use `_` for a single character -- so I think `like '_00%'` would work – James Z Oct 13 '22 at 17:54
  • Diagnosis codes won't necessarily have a common format in some groupers, so there may be occasions where `A00%` are related to `B10%`. You may want to download a CCS grouper table from AHRQ. That has some standard groups that may be helpful to you. – Isolated Oct 13 '22 at 19:04

1 Answers1

0

First position is a letter in the range A-Z, then a 0, then a 0, then anything.

select Diagnosis_Cd
  from <table>
 where Diagnosis_Cd like '[A-Z][0][0]%'
Gary_W
  • 9,933
  • 1
  • 22
  • 40