560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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!
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:
SOUNDEX(Fruit) as Sound
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).
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:
SOUNDEX(Fruit) as Sound,
DIFFERENCE(Fruit,'Lemurs') AS '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:
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?
|Parts of this blog|
25 Aytoun Street