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:

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.

Hvis du vil vide mere om SOUNDEX index systemet så kan du læse mere her:

https://www.archives.gov/research/census/soundex.html

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

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');

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

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');

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

SELECT SOUNDEX('HEST'),
       SOUNDEX('TESH');

SELECT DIFFERENCE('HEST','TESH') AS soundex_diff;

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:

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

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. SOUNDEX er et gammelt system oprindelig udviklet til at ”give navne lyd” hvis du er interesseret kan du læse mere om det her: https://www.genealogy.com/articles/research/00000060.html

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ø.