1-- Simple SQL UDF
2CREATE OR REPLACE FUNCTION mask_email(email VARCHAR)
3RETURNS VARCHAR
4LANGUAGE SQL
5AS
6$$
7CONCAT(LEFT(email, 2), '****@', SPLIT_PART(email, '@', 2))
8$$;
9
10-- Usage
11SELECT mask_email('john.smith@company.com');
12-- Result: jo****@company.com

1-- JavaScript UDF
2CREATE OR REPLACE FUNCTION calculate_bmi(weight_kg FLOAT, height_m FLOAT)
3RETURNS FLOAT
4LANGUAGE JAVASCRIPT
5AS
6$$
7return WEIGHT_KG / (HEIGHT_M * HEIGHT_M);
8$$;
9
10-- Note: JavaScript UDFs receive column names in UPPERCASE

1-- Python UDF (requires Snowpark)
2CREATE OR REPLACE FUNCTION sentiment_score(text VARCHAR)
3RETURNS FLOAT
4LANGUAGE PYTHON
5RUNTIME_VERSION = '3.8'
6HANDLER = 'score'
7AS
8$$
9def score(text):
10  positive = ['good', 'great', 'excellent', 'love']
11  words = text.lower().split()
12  matches = sum(1 for w in words if w in positive)
13  return matches / max(len(words), 1)
14$$;

1-- UDTF that splits a string into rows
2CREATE OR REPLACE FUNCTION split_to_rows(input VARCHAR, delimiter VARCHAR)
3RETURNS TABLE (value VARCHAR, position INT)
4LANGUAGE JAVASCRIPT
5AS
6$$
7{
8processRow: function(row, rowWriter, context) {
9  var parts = row.INPUT.split(row.DELIMITER);
10  for (var i = 0; i < parts.length; i++) {
11    rowWriter.writeRow({VALUE: parts[i].trim(), POSITION: i + 1});
12  }
13}
14}
15$$;
16
17-- Usage: call with TABLE() and OVER()
18SELECT t.value, t.position
19FROM TABLE(split_to_rows('apple,banana,cherry', ',')) AS t;

1-- SQL Scripting stored procedure
2CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
3RETURNS VARCHAR
4LANGUAGE SQL
5AS
6$$
7BEGIN
8INSERT INTO sales.orders_archive
9SELECT * FROM sales.orders WHERE order_date < :cutoff_date;
10
11DELETE FROM sales.orders WHERE order_date < :cutoff_date;
12
13RETURN 'Archive complete';
14END;
15$$;
16
17-- Call the procedure
18CALL archive_old_orders('2023-01-01');

1-- JavaScript stored procedure with error handling
2CREATE OR REPLACE PROCEDURE refresh_summary(schema_name VARCHAR)
3RETURNS VARCHAR
4LANGUAGE JAVASCRIPT
5EXECUTE AS OWNER
6AS
7$$
8try {
9  var stmt = snowflake.createStatement({
10    sqlText: 'TRUNCATE TABLE ' + SCHEMA_NAME + '.daily_summary'
11  });
12  stmt.execute();
13
14  stmt = snowflake.createStatement({
15    sqlText: 'INSERT INTO ' + SCHEMA_NAME + '.daily_summary SELECT * FROM ' + SCHEMA_NAME + '.v_daily_summary'
16  });
17  var result = stmt.execute();
18  return 'Refreshed ' + result.getNumRowsInserted() + ' rows';
19} catch (err) {
20  return 'Error: ' + err.message;
21}
22$$;

1-- Step 1: Create API integration
2CREATE API INTEGRATION my_api_integration
3API_PROVIDER = aws_api_gateway
4API_AWS_ROLE_ARN = 'arn:aws:iam::123456789:role/my-role'
5API_ALLOWED_PREFIXES = ('https://abc123.execute-api.us-east-1.amazonaws.com/')
6ENABLED = TRUE;
7
8-- Step 2: Create the external function
9CREATE EXTERNAL FUNCTION translate_text(text VARCHAR, target_lang VARCHAR)
10RETURNS VARCHAR
11API_INTEGRATION = my_api_integration
12AS 'https://abc123.execute-api.us-east-1.amazonaws.com/prod/translate';
13
14-- Step 3: Use like any function
15SELECT translate_text(description, 'fr') FROM products;