Setting SQL criteria using the WHERE clause
Part four of a four-part series of blogs

If you don't want to see a full set of records from a table in SQL, you can use a WHERE statement to filter out unwanted rows, as shown in this blog.

1. The WHERE Clause in SQL to filter records using criteria
2. WHERE Criteria with Text Fields: % and _ Wildcards
3. Combining WHERE Criteria
4. Using Sound in Criteria (this blog)

This blog is part of our online SQL tutorial blog.  If you want to learn SQL in a classroom environment, have a look at our SQL training courses.

Posted by Andy Brown on 08 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Using Sound in Criteria

I'm not sure it has any place in a blog which tries hard to be useful, but for the sake of completeness I feel I ought to mention the DIFFERENCE function.

The theory behind this is OK - that you can search for people whose names sound like Smith, say, following a phone enquiry - but in practice the algorithm doesn't work, as the examples on this page will show!

## The SOUNDEX Function

If you take any string of text, you can use the SOUNDEX function to assign to it a 4-character numerical code representing its sound (where the first character represents the first letter and the other 3 characters represent the sounds of the other symbols).  Here is some SQL showing the value of this function for different fruit:

SELECT

Fruit,

SOUNDEX(Fruit) as Sound

FROM

tblFruit

ORDER BY

SOUNDEX(Fruit)

This would give the following output:

List of fruit with 4-character sound representations.

This shows that the sound expressions for the Blueberries/Bananas and Limes/Lemons pairs each start with the same character (B and L respectively).

## The DIFFERENCE Function

Building on the SOUNDEX function above, you can use the DIFFERENCE function to represent how similar two sounds are.  The values returned are:

Value What it means
0 The two sounds are completely different.
1 A tiny bit of similarity between the sounds.
2 Some similarity between the sounds.
3 Lots of similarity between the sounds.
4 The sounds are more or less the same.

Here's some SQL to test out the sound difference between the fruit names in our database and the word Lemurs:

SELECT

Fruit,

SOUNDEX(Fruit) as Sound,

DIFFERENCE(Fruit,'Lemurs') AS 'Sound rating'

FROM

tblFruit

ORDER BY

'Sound rating'

And here's what it returns:

I can accept that Lemons and Lemurs sound the same, and that Limes and Lemurs sound similar. But Bananas and Lemurs?

In practice the algorithm is so imprecise that it's close to useless, but here's how to show all of the actors in the Wise Owl movies database whose names sound like mine:

SELECT

ActorName,

Difference(ActorName,'Andy Brown')

FROM

tblActor

WHERE

Difference(ActorName,'Andy Brown') >=3

And the result?

How can Andy Serkis and Andy Brown sound the same?  And what do Timothy Olyphant and Andy Brown have in common phonetically?

I've included the sound functions for completeness, but as far as I can see they are next to useless!  Unless anyone begs to differ?