Heb jij altijd al je eigen functie willen definiëren? Dan heb ik hieronder een mooie use-case voor je.

In #Snowflake bestaat er een functie TRY_TO_NUMBER. Deze functie probeert waardes in een kolom van het datatype VARCHAR om te zetten naar een nummer. Voor degenen die niet bekend zijn met deze term, VARCHAR is een datatype wat zo veel betekent als datatype = tekst. Stel dat er zich in de kolom een waarde bevindt die niet omgezet kan worden naar een nummer zoals de waarde ‘appel’, dan komt hier een lege waarde te staan (NULL).

So far so good. Dit is precies wat je wilt van een functie als TRY_TO_NUMBER.

Echter, wanneer er een ‘e’ of ‘E’ staat, dan houdt de functie TRY_TO_NUMBER rekening met het feit dat dit een ‘e’ kan zijn uit de wetenschappelijke notatie en maakt er een 0 van. Grr, dat is niet wat ik verwachtte van deze functie in deze situatie!

Je zou dit op kunnen lossen met CASE WHEN, maar waarom maken we er niet zelf een functie voor? Een zelfgemaakte functie ofwel User Defined Functie (UDF), kan hier namelijk een oplossing voor bieden. In de onderstaande UDF maken we een nieuwe functie die een NULL retourneert wanneer er een ‘e’ of ‘E’ staat en in andere gevallen de functie TRY_TO_NUMBER gebruikt. Zo lossen we de “tekortkomingen” van TRY_TO_NUMBER op.

CREATE OR REPLACE FUNCTION SOME_DB.ANOTHER_SCHEMA.UDF_TRY_TO_NUMBER(X, VARCHAR)
RETURNS INT

LANGUAGE SQL

/* In Snowflake: whenever you use the native function TRY_TO_NUMBER and you feed the function a VARCHAR with an ‘E’ or ‘e’,
the result will be a 0 because the function takes the ‘e’ of scientific notation into account */

AS

$$
SELECT IFF(X = ‘e’ OR x = ‘E’, NULL, TRY_TO_NUMBER(X))
— If the value you are trying to convert to int contains an ‘e’ or ‘E’, return a NULL, else use the function TRY_TO_NUMBER
$$
;