SQL Query similar to Grep functionality

Question

I have the following bash command:

grep -P 'The Beatles' | wc -l

I need to create an SQL query that imitates the same functionality. The table schema is as follows:

track_id    
sales_date  
sales_count
title
song_id 
release 
artist_id   
artist_mbid 
artist_name 
duration    
artist_familiarity  
artist_hotttnesss
year

Sample data:

TRZZZZZ12903D05E3A,2014-06-19,79,Infra Stellar,SOZPUEF12AF72A9F2A,Archives Vol. 2,ARBG8621187FB54842,4279aba0-1bde-40a9-8fb2-c63d165dc554,Delerium,495.22893,0.69652442519,0.498471038842,2001

I have written the following code but it fails to do a case sensitive match.

SELECT Count(track_id) FROM songs WHERE track_id LIKE '%The Beatles%' OR title LIKE '%The Beatles%' OR song_id LIKE '%The Beatles%' OR release LIKE '%The Beatles%' OR artist_id LIKE '%The Beatles%' OR artist_mbid LIKE '%The Beatles%' OR artist_name LIKE '%The Beatles%' OR duration LIKE '%The Beatles%' OR artist_familiarity LIKE '%The Beatles%' OR artist_hotttnesss LIKE '%The Beatles%' OR year LIKE '%The Beatles%'

I tried using COLLATE Latin1_General_CS_AS and COLLATE Latin1_General_BIN but it gives me the following error:

ERROR 1273 (HY000) at line 1: Unknown collation: 'Latin1_General_BIN'

Anyway I could do a case sensitive matching? Thanks!

See http://stackoverflow.com/questions/4838079/sql-like-syntax — ccarton, Feb 28 '15 at 11:19
What database are you using? It would appear to be MySQL, but you should explicitly tag the question. — Gordon Linoff, Feb 28 '15 at 12:18

score 2 · Accepted Answer · answered Feb 28 '15 at 12:24

A simple way to get case sensitive comparisons in MySQL that usually works is to use binary:

SELECT Count(track_id)
FROM songs
WHERE track_id LIKE BINARY '%The Beatles%' OR
      title LIKE BINARY '%The Beatles%' OR
      song_id LIKE BINARY '%The Beatles%' OR
      release LIKE BINARY '%The Beatles%' OR
      artist_id LIKE BINARY '%The Beatles%' OR
      artist_mbid LIKE BINARY '%The Beatles%' OR
      artist_name LIKE BINARY '%The Beatles%' OR
      duration LIKE BINARY '%The Beatles%' OR
      artist_familiarity LIKE BINARY '%The Beatles%' OR 
      artist_hotttnesss LIKE BINARY  '%The Beatles%' OR
      year LIKE BINARY '%The Beatles%';

By the way, this type of problem is just saying "use a full text index, use a full text index". You can read about that here.

SQL Query similar to Grep functionality

1 Answers1