Soundex Value

Jeg elsker at lære og undersøge. Derfor har jeg sat mig for at prøve nogle af alle de funktioner, der er i SQL Serveren, som jeg sjældent eller aldrig bruger.

I dag vil jeg prøve SOUNDEX, som på docs beskrives på følgende måde ” SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken.”

Altså hvis jeg skriver følgende Query:

SELECT SOUNDEX('Unit it') AS Soundex_value

Får jeg værdien U530 retur:

sql-soundex_01

Men hvorfor nu det? Først må vi forstå hvordan SOUNDEX genereres.

Første karakter er første bogstav i stringen, og derefter benyttes følgende regler:

Vokaler undlades med mindre det er det første i stringen, bogstaverne får følgende værdi:

1                       B, F, P, V

2                       C, G, J, K, Q, S, X, Z

3                       D, T

4                       L

5                       M, N

6                       R

Følgende bogstaver undlades: A, E, I, O, U, H, W, og Y.

Hvis der er dobbelt bogstav, så tages værdien for det første og er der ikke nok karakterer til at lave en 4 karakters kode, så tilføjes 0 er til sidst.

Så hvis vi vil have SOUNDEX værdien for Abe giver det altså værdien A100 fordi A er første bogstav, b = 1, e undlades og så tilføjes der 2 x 0 til sidst:

Query:

SELECT SOUNDEX('Abe') AS Soundex_value
sql-soundex_02

Difference funktionen

Hvad kan vi så bruge det til? – Ja det er et godt spørgsmål, og det har jeg også spurgt mig selv om mange gange. Men hvis vi nu kigger på DIFFERENCE funktionen, som har følgende syntax:

DIFFERENCE ( character_expression , character_expression )

Funktionen benytter SOUNDEX til at give en værdi mellem de 2 udtryk og fortæller hvor ens de er. Skalaen går fra 0 til 4 hvor 0 = meget usandsynlig og 4 = ret sandsynligt. Altså hvis 3 af de 4 karakterer er ens, får vi 3 retur. Og så begynder SOUNDEX pludselig at give mening og værdi. For nu kan vi bruge DIFFERENCE til at finde stringe som f.eks. har en stavefejl, eller er stavet næsten ens.

Et eksempel kunne være vejnavne f.eks. Grenåvej og Grenaavej:

SELECT SOUNDEX('Grenåvej'),
SOUNDEX('Grenaavej');
sql-soundex_03

Hvorfor er det nu lige, at værdierne er ens nu? G er det første bogstav i stringen R = 6, e undlades, n= 5, å undlades og v = 1. I Grenaavej her undlades begge aa da vokaler undlades:

Så i dette eksempel hvis vi bruger DIFFERENCE funktionen får vi 4 som synlighed retur:

SELECT DIFFERENCE('Grenåvej','Grenaavej') AS soundex_diff
sql-soundex_04

Hvis vi derimod kigger på vejnavn Århusvej og Aarhusvej så får vi Å621 og A621 retur forskellen er start bogstavet:

SELECT SOUNDEX('Århusvej'),
SOUNDEX('Aarhusvej');
sql-soundex_05

Hvis vi nu prøver at køre DIFFERENCE funktionen på de 2 stringe, så får vi 3 retur fordi 621 er ens i begge SOUNDEX:

SELECT DIFFERENCE('Århusvej','Aarhusvej') AS soundex_diff
sql-soundex_06

Bare for at se hvordan DIFFERENCE fungerer, så lad os prøve at vende stringen om.

Lad os prøve med HEST og TESH. Her vil SOUNDEX værdierne være H230 og T200 hvilket burde give en DIFFERENCE på 2. (fordi X2X0 matcher i begge stringe)

Lad os teste:

SELECT SOUNDEX('HEST'),
SOUNDEX('TESH');
sql-soundex_07
SELECT DIFFERENCE('HEST','TESH') AS soundex_diff;
sql-soundex_08

Lad os prøve med et lidt større eksempel:

Jeg opretter en tabel med vejnavn, vejnummer og postnummer:

CREATE TABLE dbo.SoundEx_test(
Street VARCHAR(40),
Number INT,
Postal_code INT
);
GO

DECLARE @id INT = ABS(CHECKSUM(NEWID()) % 2) + 1

;WITH str_names
AS(
SELECT str_name, id
FROM
(VALUES (1,'Grenåvej'), (1,'Grenaavej'), (2,'Århusvej'), (2,'Aarhusvej')) street (id, str_name)
)
INSERT INTO SoundEx_test (Number, Postal_code,Street)
SELECT FLOOR(RAND()*100),
8000,
s.str_name
FROM str_names s
WHERE s.id = @id;
GO 200

Jeg indsætter 200 rækker med vilkårlige vejnumre og benytter vejnavnene fra eksemplet længere oppe. Data ser sådan her ud:

sql-soundex_09

Jeg kan så køre følgende Query for at få fat i alle de adresser hvor DIFFERENCE er 3 eller derover, altså adresser der er næsten ens:

DECLARE @search_str VARCHAR(40) = 'Aarhusvej'

SELECT s.*,
SOUNDEX(s.Street) AS SoundEx_value,
DIFFERENCE(s.Street, @search_str) Difference_value
FROM dbo.SoundEx_test s
WHERE DIFFERENCE(s.Street, @search_str) >= 3
sql-soundex_010

Resultatet af Soundex og Difference

Ja det er måske ikke et fantastisk eksempel 😊 men forhåbentlig giver det en ide om hvordan SOUNDEX og DIFFERENCE fungerer og kan bruges. 

Tak fordi du læste med. Som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, hvis du har spørgsmål eller udfordringer med dit SQL miljø.