This is not intended to be an exhaustive guide to using SQL queries, as there are a lot of documentation out there that do a good job on explaining how to use the SQLite SQL syntax. This is only a compilation of the most common type of queries you might use in DICOM Search.
The basics
A basic SQL command selects columns from the tags table e.g.
SELECT * FROM tags
As there can be hundreds or thousands of columns in that table, you would usually select only the columns you want to view the values for e.g.
SELECT ds_thumbnail, ds_filename, [00080060] FROM tags
DICOM tags are identified by the square brackets surrounding 8 numbers, while columns starting with the ds_ prefix are file metadata values. See this topic for more information. DICOM Search makes it easy to find the tag values you want to search or filter on by providing a special Tags and fields window that lists out all the available columns.
To display this window, press the F2 key when your cursor is in the query area.
To apply search criteria to your SQL query, use the WHERE clause e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] = 'US'
In this example, we want to retrieve only images where the modality ([00080060] is the modality tag) is ultrasound. You can search for multiple values for a tag using the IN operator e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] IN ('US', 'CT', 'MR')
The above query returns only images where the modality is ultrasound, computed tomography or magnetic resonance. You can combine multiple search conditions using the AND and OR operators e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] IN ('US', 'CT', 'MR') AND [00081090] = 'UltraPACS'
The above query adds a search condition where the manufacturer model name is UltraPACS. If written this way:
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] IN ('US', 'CT', 'MR') OR [00081090] = 'UltraPACS'
then the results would include all images where the modality is one of the 3 entered types, or where the manufacturer model name is UltraPACS.
You can combine multiple AND and OR operators, and use brackets to make clear which conditions are applied individually and which conditions are grouped e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE ([00080060] IN ('US', 'CT', 'MR') AND [00081090] = 'UltraPACS') OR [00080080] = 'Connlab'
Working with text values
Text values used in DICOM Search SQL queries are case-sensitive, so the following 2 SQL queries will not return the same results:
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] = 'US'
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] = 'us'
To perform a case-insensitive search on text values, use the LIKE operator e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] LIKE 'us'
In this way, you do not need to know the actual tag value. You can also convert the value to either upper-case or lower-case before making the comparison e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE LOWER([00080060]) = 'us'
The LIKE operator can also be used to look for partial text values. For e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080080] LIKE 'Connlab%'
will return images where the institution name starts with the value Connlab. This query
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080080] LIKE '%Connlab'
will return images where the institution name ends with the value Connlab. This query
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080080] LIKE '%Connlab%'
will return images where the institution name ends contains the value Connlab.
Working with numbers
You can use the equality symbol for numbers e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00200011] = 5
will return images where the series number ([00200011]) value is 5. You can also use comparison operators e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00200011] > 5
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00200011] >= 5
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00200011] < 5
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00200011] <= 5
Some tags store numbers with a prefix or suffix. For e.g. the age tag [00101010] may store the age like this:
This prevents you from using comparison operators like > or <. To workaround this, use the EXTRACTNUMBER function to extract numbers from the tag value. For e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE EXTRACTNUMBER([00101010]) > 50
will return only images where the patient age is over 50. The EXTRACTNUMBER function will extract the first sequence of numbers that it finds. If none is found, it returns the value 0. Note that you can use aliases in place of tag values e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE EXTRACTNUMBER(age) > 50
assuming that you have defined age as the alias for tag [00101010].
There is also an EXTRACTNUMBERDEF function that accepts a default value which is returned if the tag value does not contain a number. For e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE EXTRACTNUMBERDEF(age, 51) > 50
will return all images where the patient age is over 50. For images where the [00101010] tag does not contain any numbers, the value 51 is returned, and thus will be returned as part of the search results.
Working with date and time tag values (DA and TM types)
Date and time values are stored as numbers in the database, using the DICOM format. For dates, this is in the format yyyymmdd, so 10 March 2021 will be stored as 20210310.
Similarly, time values are stored in the format hhnnss.ffffff where ffffff represents a fractional part of a second, so 10:52 AM will be stored as 105200.0.
To search for date and time values, you need to treat the values as numbers. For e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080020] = 20200108
will return images where the study date ([00080020]) is on January 8, 2020. You can also search within a range of dates e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080020] >= 20200108 AND [00080020] < 20200401
will return images where the study date ([00080020]) is between 8 January 2020 and 1 April 2020.
For time values, this example:
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080030] = 220508
will return images where the study time ([00080030]) is 10:05:08 PM. Like numbers, you can search for a range of time values e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080030] >= 010000 AND [00080020] <= 020000
will return images where the study time ([00080030]) is between 1 AM and 2 AM.
Working with date time tag values (DT tag type)
DT tag type values are stored in the format YYYYMMDDHHMMSS.FFFFFF&ZZXX. If the value does not contain the timezone offset values (&ZZXX), you can treat the value as one large number and write your search conditions accordingly. E.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [0008002A] > 20200118220500
will return images where the Acquisition Date / Time ([0008002A]) value is later than 18 January 2020 10:05 PM.
If the tag value contains timezone offsets, you need to use the EXTRACTNUMBER function to discard the timezone offsets, as DICOM Search does not yet support search conditions using the timezone offset values. For e.g. if your tag value is
20200118220500.000000+0830
You need to use this query to remove the +0830 value:
SELECT ds_thumbnail, ds_filename FROM tags WHERE EXTRACTNUMBER([0008002A]) = 20200118220500
Sorting
The ORDER BY operator is used to return your results in a specific order. E.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE DATETIME([00080020]) >= '2020-01-08' ORDER BY [00080020]
will return the results ordered by study date. To return the results in descending order, use the DESC qualifier e.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE DATETIME([00080020]) >= '2020-01-08' ORDER BY [00080020] DESC
Aggregation
There are SQL functions that allow you to summarize your search results. The COUNT function returns the number of entries matching your search criteria without returning each individual entry e.g.
SELECT COUNT(*) FROM tags WHERE [00080060] = 'US'
returns the number of images where the modality value is ultrasound. You can also use the GROUP BY function when you need to group your search results. For e.g. if we wanted to know the number of images we have grouped by modality, we could run the following SQL query:
SELECT [00080060], COUNT(*) FROM tags GROUP BY [00080060]
which returns the following on our test database:
You can group by multiple tag values e.g. we could group the above results further by instituition name ([00080080]) using
SELECT [00080060], [00080080], COUNT(*) FROM tags GROUP BY [00080060], [00080080]
which returns the following on our test database:
To apply a filter to the grouped values, use the HAVING function. For e.g. if we want to display only groups with 100 entries or more from the above query, we enter the following SQL:
SELECT [00080060], [00080080], COUNT(*) FROM tags GROUP BY [00080060], [00080080] HAVING COUNT(*) > 100
Limiting the number of rows returned
To return only a specific number of rows from your SQL query, use the LIMIT function. E.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] = 'US' LIMIT 10
returns only the first 10 rows from the result set. The rows that are returned are dependent on the sorting order. E.g.
SELECT ds_thumbnail, ds_filename FROM tags WHERE [00080060] = 'US' ORDER BY [00080020] LIMIT 10
returns the latest 10 rows sorted by study date ([00080020]).