> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Clean text and convert types

> Normalize text, extract fields, and convert values safely.

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/dates-and-time.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.