Little-Known Ways to Clean Text Data in SQL

How Forgotten String Functions Clean Data Better Than Python

---

---

When you work with databases, the strings and text data are the most common and complicated part of the data transformation in SQL.

Today I’m going to show you how you can easily deal with any text data you will encounter in your databases and SQL Server code examples.

---

String Concatenation and Formatting:

FORMAT:

This function formats values as text. Use this when you need commas, currency, or dates in a specific style.

SELECT
  customer_name,
  FORMAT(order_total, ‘C’, ‘en-US’) AS total_usd, -- $1,234.50
  FORMAT(order_date, ‘MMMM dd, yyyy’) AS placed_on, -- June 27, 2026
  FORMAT(quantity, ‘N0’) AS qty_with_commas -- 1,234
FROM 
  orders;

CONCAT:

The CONCAT function joins two or more strings together. Pass the columns as arguments.

SELECT 
  CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM 
  users;

In SQL Server, CONCAT treats NULL arguments as empty strings, so CONCAT(‘A’, NULL, ‘B’) returns ‘AB’.

CONCATWS:

The function takes a separator as the first argument and joins the remaining arguments.

SELECT 
  CONCAT_WS(’, ‘, city, state, zip_code) AS full_address
FROM 
  locations;

The engine inserts the separator between non-null values and skips null entries.

| | :

Double pipe is the concatenation operator:

SELECT
    FirstName || ‘ ‘ || MiddleName || ‘ ‘ || LastName AS FullName
FROM 
  Person
LIMIT 10

STUFF:

STUFF removes a substring at a specified position and inserts another string in SQL Server.

SELECT STUFF(’ABC-12345-XYZ’, 5, 5, ‘99999’) AS UpdatedCode;
-- Output: ABC-99999-XYZ

It takes the original string, and then you specify the position where it will start, the length of the chunk, and the replacement value.

Split and aggregate:

REGEXP_SPLIT_TO_TABLE:

The function splits an input string into rows using a regular expression pattern as the delimiter.

SELECT 
  value, 
  ordinal
FROM 
  REGEXP_SPLIT_TO_TABLE(’utm_source=linkedin 
  | utm_medium=cpc   
  | utm_campaign=powerbi_launch’, ‘\s*\|\s*’)

Use it when delimiters vary: whitespace, multiple characters, mixed punctuation.

STRING_SPLIT:

It divides a string into rows of substrings based on a specified character separator

SELECT 
  value, 
  ordinal
FROM 
  STRING_SPLIT
  (’utm_source=linkedin&utm_medium=cpc&utm_campaign=powerbi_launch’, ‘&’, 1)

Use STRING_SPLIT for single-character delimiters. The ordinal output is available only in SQL Server 2022 and later.

You could also pair it with the TRIM function to clean and split comma-separated list:

SELECT 
  TRIM(value) AS server_name, ordinal
FROM 
  STRING_SPLIT(’PRODDB01, STAGEDB02, REPORTDB03’, ‘,’, 1)

---

Text transformation:

Case Transformation:

The UPPER and LOWER functions convert all alphabetic characters to either lower or upper case.

SELECT 
  UPPER(country_code) AS standardized_code
FROM 
  addresses

Trimming:

TRIM:

The TRIM function removes both leading and trailing spaces from a string.

SELECT TRIM(’   database   ‘);
-- Result: database

LTRIM and RTRIM:

LTRIM removes only leading spaces. RTRIM removes only trailing spaces.

SELECT LTRIM(’   database’);   -- Result: database
SELECT RTRIM(’database   ‘);   -- Result: database

Replacement:

TRANSLATE:

The function performs 1:1 character mapping. For digit masking, use a regex replace function where available, or use nested REPLACE calls.

For example, you can replace the personally identifiable data with the placeholders.

SELECT 
  TRANSLATE(credit_card, ‘0123456789’, ‘XXXXXXXXXX’) AS masked_card
FROM 
  transactions;

REPLACE:

The REPLACE function searches a string for a specified substring and replaces every occurrence with another substring. It takes three arguments: the source string, the substring to find, and the replacement.

SELECT REPLACE(’123-456-7890’, ‘-’, ‘/’);
-- Result: 123/456/7890

To remove characters, supply an empty string as the replacement.

SELECT REPLACE(’123-456-7890’, ‘-’, ‘’);
-- Result: 1234567890

---

Text Length and Byte Measurement:

LEN:

The function returns the number of characters in a string.

SELECT 
  LEN(’Hello’) AS Plain; -- 5

SELECT 
  LEN(’Hello ‘) AS Trailing; -- 5, the space is ignored

SELECT 
  LEN(’ Hello’) AS Leading; -- 6, leading space counts

SELECT 
  LEN(’‘) AS Empty; -- 0

SELECT 
  LEN(NULL) AS NullInput; -- NULL

DATALENGTH:

The function provides the storage size of a value in bytes instead of the number of characters.

Different data types affect storage.

A VARCHAR string uses 1 byte per character, while an NVARCHAR (Unicode) string uses 2 bytes per character.

SELECT 
    DATALENGTH(CAST(’Power BI’ AS VARCHAR(50))) AS varchar_bytes,
    DATALENGTH(CAST(’Power BI’ AS NVARCHAR(50))) AS nvarchar_bytes

Use it for performance optimization and storage audit, as it reveals the true underlying storage size, and shows data type inefficiencies.

CHARINDEX:

The function searches for a specific substring inside a larger string and returns its starting position as an integer.

If the function finds the substring, it returns its index position. If it cannot find the substring, it returns 0.

SELECT 
  CHARINDEX(’\’, ‘PRODDB01\INSTANCEA’) AS backslash_position

You can also provide an optional third argument with character position where the search should begin.

PATINDEX:

The function searches a string for the first occurrence of a pattern and returns its starting position as an integer.

SELECT 
    PATINDEX(’%[0-9]%’, ‘PRODDB01\INSTANCEA’) AS first_digit_position

In this example, SQL locates where a numeric identifier or version number begins inside a text field.

'%[0-9]%' finds the first character that is a digit between 0 and 9.

---

Search Operations and Part extraction:

LEFT:

The LEFT function grabs characters from the beginning.

SELECT RIGHT(transaction_id, 4) AS last_four
FROM payments;

RIGHT:

The RIGHT function grabs characters from the end.

SELECT 
  RIGHT(transaction_id, 4) AS last_four
FROM 
  payments

SPLIT_PART:

The SPLIT_PART function divides a string and returns a specific segment.

SELECT 
  SPLIT_PART(file_path, ‘/’, 3) AS directory
FROM logs

SUBTSRING:

The SUBSTRING function isolates text based on a starting position and a length parameter.

SELECT 
  SUBSTRING(user_email, 1, POSITION(’@’ IN user_email) - 1) AS username
FROM 
  customers;

---

REGEX:

REGEXP_COUNT:

The function returns the number of times a regular expression pattern matches within a target string.

SELECT 
  REGEXP_COUNT(’DAX|Power_BI|Data_Modeling|Query_Folding’, ‘\b[A-Za-z_]+\b’)
   AS tag_count

In this example SQL scans a string of tags or audit categories to determine how many separate attributes are present by matching word boundaries.

\b[A-Za-z_]+\b matches individual whole words and ignoring separators: pipes or spaces.

REGEXP_SPLIT_TO_TABLE:

It takes an input string and splits it into rows with a regular expression pattern as the divider.

SELECT 
  value, 
  ordinal
FROM 
  REGEXP_SPLIT_TO_TABLE(’DAX, Power BI,   Data Modeling,SQL Optimization’,
   ‘,\s*’)

Users often enter descriptive tags with messy spacing. This example uses the pattern ,\s* to split tags cleanly at each comma while wiping out any trailing spaces before the next tag name.

REGEXP_SUBSTR:

The function finds the first occurrence of a pattern.

SELECT 
  REGEXP_SUBSTR(log_message, ‘ERROR:\s+\d+’) AS error_code
FROM 
  system_logs;

REGEXP_REPLACE:

The REGEXP_REPLACE function swaps matched sequences with a new string.

SELECT 
  REGEXP_REPLACE(phone_number, ‘[^0-9]’, ‘’) AS digits_only
FROM 
  contacts;

REGEXP_LIKE:

This function checks whether a text string matches a specified regular expression pattern. It returns true if the pattern matches, and false if it does not match.

-- Find employees with valid internal corporate email formats
SELECT 
  EmployeeID, 
  FirstName, 
  Email
FROM 
  Employees
WHERE 
  REGEXP_LIKE(Email, ‘^[A-Za-z0-9._%+-]+@company\.com$’, ‘i’);

This script selects records where an email address follows a specific pattern.

REGEXP_MATCHES:

Unlike REGEXP_LIKE (which returns a simple true/false value), REGEXP_MATCHES in SQL Server extracts every occurrence of a pattern from your text and outputs the results as a structured table.

SELECT 
    match_id,
    start_position,
    match_value
FROM 
  REGEXP_MATCHES(’Office: 555-1234, Cell: 555-8765’, ‘\d{3}-\d{4}’);

REGEXP_INSTR:

It searches a string for a regular expression pattern and returns the starting character position of the match. If the function finds no match, it returns 0.

SELECT 
  REGEXP_INSTR(’System alert at 10:15 AM: ERR404 connection timeout.’,
   ‘ERR\d{3}’) AS ErrorPosition;

The query searches a log message to find the index position of the first error code matching the pattern “ERR” followed by three digits.

REGEXP Flags:

You can pass optional character arguments into native regular expression functions to change how patterns are matched.

While standard SQL operators like LIKE are case-insensitive by default in many database collations, regular expression engines within SQL databases are often case-sensitive until they are overridden by a flag.

| Flag | Description                                        |
| ---- | -------------------------------------------------- |
| `i`  | Case-insensitive matching                          |
| `c`  | Case-sensitive matching (default)                  |
| `m`  | Multi-line mode: `^` and `$` match line boundaries |
| `s`  | Dot-all mode: `.` matches newline characters       |
| `g`  | Global. Replaces or extracts all occurrences       |
| `x`  | Ignore whitespace                                  |

---

Performance issues:

• Native regex functions are not SARGable (cannot efficiently use indexes).

• REGEXP_LIKE in a WHERE clause forces an index scan.

• Long strings split with REGEXP_SPLIT_TO_TABLE or STRING_SPLIT consume massive memory across millions of rows, forcing SQL Server to dump data onto slow tempdb disk storage. Filter your source data to the minimum required rows before you apply split functions.

• Scalar operations: REGEXP_COUNT or REGEXP_REPLACE process data one row at a time, which stops query parallelism on large tables. Apply these regex evaluations only to pre-filtered subsets rather than entire datasets.

• The FORMAT function is slow. Mismatched VARCHAR and NVARCHAR types force implicit conversions that make indexes useless. Use CONVERT for standard dates and match your variable types to your table columns.

• TRIM, UPPER, or REPLACE inside an ON join or GROUP BY clause prevents the query optimizer from calculating data distribution and forces slow nested loop joins. Standardize and index your text columns during your data loading phase instead of transforming text during live queries.

---

Follow me for more data cleaning strategies.

What’s the most underrated SQL string function?

Let me know in the comments👇

P.S. I’m launching an advanced Power BI performance guide.

Join the waitlist → https://newsletter.mikhailmikushin.com/waitlist-power-bi-performance-optimization

---