Clean text and convert types

TutorialApplies to: Unified engine deployments

Normalize text, extract fields, and convert values safely.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Normalize case

Can names use the same case?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id, LOWER(name) AS name FROM contacts ORDER BY id;

Expected result

id	name
1	ada
2	ben

LOWER changes case and keeps spaces. Change one thing: Use UPPER. Expect uppercase names with the same spaces.

2. Remove edge spaces

Can we remove surrounding spaces?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id, LTRIM(RTRIM(name)) AS name FROM contacts ORDER BY id;

Expected result

id	name
1	Ada
2	BEN

LTRIM and RTRIM remove spaces on the left and right. Change one thing: Apply LOWER around the expression. Expect ada and ben.

3. Combine labels

Can we build a readable contact label?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id, CONCAT_WS(': ', code, LTRIM(RTRIM(name))) AS label FROM contacts ORDER BY id;

Expected result

id	label
1	UK-101: Ada
2	US-202: BEN

CONCAT_WS joins values with a separator. Change one thing: Change the separator to / . Expect UK-101/ Ada and US-202/ BEN.

4. Extract a prefix

What country prefix does each code contain?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id, SPLIT_PART(code, '-', 1) AS country FROM contacts ORDER BY id;

Expected result

id	country
1	UK
2	US

SPLIT_PART selects a delimited field. Field positions start at one. Change one thing: Use field position 2. Expect 101 and 202.

5. Replace text

Can codes use a slash separator?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id, REPLACE(code, '-', '/') AS code FROM contacts ORDER BY id;

Expected result

id	code
1	UK/101
2	US/202

REPLACE substitutes matching text. Change one thing: Replace - with an empty string. Expect UK101 and US202.

6. Match a pattern

Which code starts with UK?

WITH contacts(id, name, code) AS (VALUES (1, '  Ada  ', 'UK-101'), (2, '  BEN  ', 'US-202'))
SELECT id FROM contacts WHERE REGEXP_LIKE(code, '^UK-[0-9]+$') ORDER BY id;

Expected result

id
1

This regular expression checks the full code shape. Change one thing: Replace UK with US. Only ID 2 appears.

7. Convert valid numbers

Can text become a number?

WITH input(value) AS (VALUES ('12'), ('30'))
SELECT value, CAST(value AS INT) AS number FROM input ORDER BY number;

Expected result

value	number
12	12
30	30

CAST converts a value when its text is valid for the target type. Change one thing: Use DOUBLE as the target type. Numeric values stay 12 and 30.

8. Handle invalid numbers

Can invalid text become missing data?

WITH input(id, value) AS (VALUES (1, '12'), (2, 'unknown'), (3, '30'))
SELECT id, TRY_CAST(value AS INT) AS number FROM input ORDER BY id;

Expected result

id	number
1	12
2	NULL
3	30

TRY_CAST returns NULL for a failed supported conversion. Change one thing: Use COALESCE(TRY_CAST(value AS INT), 0). ID 2 returns zero.

Continue

Continue to Work with dates and time, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Clean text and convert types

Before you start

1. Normalize case

2. Remove edge spaces

3. Combine labels

4. Extract a prefix

5. Replace text

6. Match a pattern

7. Convert valid numbers

8. Handle invalid numbers

Continue

​Before you start

​1. Normalize case

​2. Remove edge spaces

​3. Combine labels

​4. Extract a prefix

​5. Replace text

​6. Match a pattern

​7. Convert valid numbers

​8. Handle invalid numbers

​Continue

Before you start

1. Normalize case

2. Remove edge spaces

3. Combine labels

4. Extract a prefix

5. Replace text

6. Match a pattern

7. Convert valid numbers

8. Handle invalid numbers

Continue