This page has 0 threads | Add post

Using Sound in Criteria

Written by Andy Brown

In this tutorial

The SOUNDEX Function
The DIFFERENCE Function

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?

Previous
tutorial

WHERE criteria (version A)

Previous
part

Combining WHERE Criteria

Top of page

Next
part

Next
tutorial

Calculations and Expressions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

WHERE criteria (version B)

Using Sound in Criteria

The SOUNDEX Function

The DIFFERENCE Function

Head office

London

Manchester