SQL Simple -> Using wildcards in MS-Access LIKE function. part 2

Here we will see next two simple examples of using wildcards within LIKE keyword in MS-Access SQL query. We are quering items table that looks like this:

ItemCodeItemName
0036876Memorex 8.5Gb/ 2.4x DVD+R Dual Layer (25-Pack Spindle)
5003258Philips DVP642 DivX-Certified Progressive-Scan DVD Player
5003298Philips DVP5140 Multiformat DVD Player with DivX, MP3, Windows Media Support
5003501TDK Electronics 4.7GB/16x DVD-R (100-Pack Spindle)
5003502TDK Electronics DVD+R47FCB100 Single-Sided 16x DVD+R Spindle, 100 Discs
5004097Verbatim 16x DVD-R 4.7 GB Discs (25-spindle)
5004423Memorex Mini DVD+RW 10 Pack
77003Panasonic DMR-ES15S DVD Recorder with DV Input


First we will restrict returned data set to Item Names that include range of numbers from 5 to 7, by writing LIKE '*[5-7]*'. In addtion we will set another LIKE filter that will look for names with words starting with character 'M' and having second character 'e' -or- 'u' ( LIKE '*M[e,u]*'). Meaning words like 'Memory' or 'Multiformat'.

SELECT ItemCode, ItemName
FROM Items
WHERE ItemName LIKE '*[5-7]*' AND ItemName LIKE '*M[e,u]*'


The query returns results like these:
ItemCodeItemName
0036876Memorex 8.5Gb/ 2.4x DVD+R Dual Layer (25-Pack Spindle)
5003298Philips DVP5140 Multiformat DVD Player with DivX, MP3, Windows Media...

Second we will add another LIKE that will exclude Item Names that starts with charcter 'P': LIKE '[!P]*'.

SELECT ItemCode, ItemName
FROM Items
WHERE ItemName LIKE '*[5-7]*' AND ItemName LIKE '*M[e,u]*'
AND ItemName LIKE '[!P]*'


And get in results only one record:
ItemCodeItemName
0036876Memorex 8.5Gb/ 2.4x DVD+R Dual Layer (25-Pack Spindle)



sqlexamples.info