Pchełki SQL: Liczy się styl

https://xpil.eu/HtOqS 📋

Pchełki SQL są na tym blogu od jakiegoś czasu traktowane po macoszemu - trochę z braku czasu, trochę z braku pomysłów, a trochę z najnormalniejszego, ludzkiego lenistwa.

Dziś nadrobimy tę zaległość. Napiszemy króciutko o funkcji skrótu oraz konwersji skrótu na tekst.

Jeżeli ktoś nie wie, co to jest funkcja skrótu, to może sobie zerknąć tutaj: https://pl.wikipedia.org/wiki/Funkcja_skrótu

Załóżmy, że importujemy zewnętrzną tabelę z danymi do hurtowni. Jedna z kolumn zawiera unikalny identyfikator, jednak okazuje się, że w niektórych przypadkach kolumna ta jest pusta (NULL). Ponieważ nie chcemy importować pustych wartości identyfikatora, możemy albo pominąć takie rekordy przy imporcie, albo zastąpić pustą wartość jakąś wartością wyliczoną na podstawie innych kolumn w rekordzie. Ważne, żeby dobrać taki zestaw kolumn, który gwarantuje unikalność.

Zobaczmy na przykładzie:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT testdata.id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Powyższe zapytanie zwróci nam id == NULL dla rekordu Cezarego Muki. Chcemy zamiast NULL-a dostać tam jakiś niepusty, unikalny identyfikator. W tym celu zastosujemy funkcję HASHBYTES:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT COALESCE(testdata.id, CONVERT(VARCHAR, HASHBYTES('MD5', kto+gdzie+kiedy))) id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Takie zapytanie zwróci nam, owszem, unikalny tekst w kolumnie ID dla rekordu pana Czarka, ale będzie to wartość powstała bezpośrednio z konwersji wartości binarnej (HASHBYTES) na tekst - czyli tzw "krzaczki". A chcielibyśmy zamiast tego dostać ciąg cyfr szesnastkowych.

I tu w sukurs przychodzi trzeci element funkcji CONVERT, czyli styl. Styl jest liczbą całkowitą mówiącą w jaki sposób ma nastąpić konwersja na tekst. W przypadku danych będących datą / czasem styli jest mnóstwo i oznaczają one rozmaite rodzaje formatowania daty i godziny. Natomiast w przypadku danych typu varbinary (zwracanych przez HASHBYTES) styl może przyjąć wartość 1 lub 2. Jedynka oznacza konwersję bezpośrednią na tekst (czyli "krzaczki") i jest domyślna, natomiast dwójka oznacza zamianę bajtów na ich reprezentację heksadecymalną. Czyli dokładnie to, o co nam chodzi. Ostateczny kod będzie więc wyglądać następująco:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT COALESCE(testdata.id, CONVERT(VARCHAR, HASHBYTES('MD5', kto+gdzie+kiedy), 2)) id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Mała, niepozorna dwójeczka sprawi, że zamiast "krzaczków" dostaniemy ładny, unikalny, szesnastkowy identyfiktor.

Voila!

https://xpil.eu/HtOqS 📋