Writing a reusable cleaning function with CREATE FUNCTION

CREATE FUNCTION saves a cleaning rule under a name, so every query can use it like a built-in function.

About 5–10 minutes · no sign-up needed

Example

Writing a cleaning rule once, as a function

CREATE OR REPLACE FUNCTION clean_text(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT INITCAP(TRIM(raw)) $$;
SELECT customer_id, clean_text(full_name) AS full_name
FROM customers

Result:

customer_id	full_name
C101	Ada Obi
C102	Ben Cole
C103	Cara Diaz

Step by step

The name, the input it takes (raw, a TEXT value) and what it returns.
```
CREATE OR REPLACE FUNCTION clean_text(raw TEXT) RETURNS TEXT
```

Between the $$ marks goes the cleaning rule: trim the spaces, then capitalise each word.

CREATE OR REPLACE FUNCTION clean_text(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT INITCAP(TRIM(raw)) $$;

Now any query can call clean_text(), exactly like TRIM() or UPPER().

CREATE OR REPLACE FUNCTION clean_text(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT INITCAP(TRIM(raw)) $$;
SELECT customer_id, clean_text(full_name) AS full_name
FROM customers;

Practice

Try each one in your head first, then open the answer.

1. Create a function called tidy(raw TEXT) that returns TRIM(raw).

CREATE OR REPLACE FUNCTION tidy(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT TRIM(raw) $$

2. clean_text() already exists. Use it to show each customer's full_name cleaned, as full_name.

SELECT clean_text(full_name) AS full_name FROM customers

3. Create clean_text(raw TEXT) that returns INITCAP(TRIM(raw)), then use it to show customer_id and the cleaned full_name, as full_name.

CREATE OR REPLACE FUNCTION clean_text(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT INITCAP(TRIM(raw)) $$; SELECT customer_id, clean_text(full_name) AS full_name FROM customers

4. Create clean_city(raw TEXT) that returns UPPER(TRIM(raw)), then show customer_id and the cleaned city, as city.

CREATE OR REPLACE FUNCTION clean_city(raw TEXT) RETURNS TEXT LANGUAGE SQL AS $$ SELECT UPPER(TRIM(raw)) $$; SELECT customer_id, clean_city(city) AS city FROM customers

5. clean_text() already exists. Show customer_id, the cleaned full_name and the cleaned city, as full_name and city.

SELECT customer_id, clean_text(full_name) AS full_name, clean_text(city) AS city FROM customers

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed