From SQHell to SQLightenment: A Girlie Guide to String Cleaning

Welcome back, darlings, to another fabulously fun instalment of SQL! This week, we’re diving into the art of tidying up string data, much like decluttering your wardrobe or applying just the right shade of lipstick to perfect your look.

You see, not all data arrives well-dressed. Sometimes it’s messy, with important nuggets buried deep—like finding the perfect account number or bank ID hiding in a labyrinth of IBAN strings. So, grab your latte, put on your cutest glasses, and let’s tackle this together!

Concatenate: The Power Couple of SQL

Let’s start with the basics—joining fields to create the SQL equivalent of a dynamic duo. This is called concatenation, and it’s perfect for when your first and last names are like separate halves of a heart pendant and need to come together. Or maybe you’re channelling your inner tech goddess and building email fields from names.

The Glue Gun Option:

Enter the CONCAT() function, which sticks everything together with commas separating the pieces.

SELECT 
  CONCAT
  (
  FirstName
  , ' '
  , LastName
  ) 
AS FullName
FROM Employees
;

Works like magic! Whether it’s John Smith or Jane Doe, you’ll have a polished result every time.

The Necklace String:

Use || to thread fields together, like beads on a string.

SELECT 
  FirstName 
  || ' ' 
  || LastName 
  AS FullName
FROM Employees
;

Result: “John Smith”

Split It Like It’s Hot

Sometimes, you need to break things down—like dissecting a text message from your ex or splitting that string of emojis into manageable chunks. Enter the fabulous SPLIT_PART() function.

Here’s how it works:

1. What’s the breakup point? Choose your delimiter (comma, dash, space, you name it).

1. Pick your favourite piece. Decide which segment of the string you need.

• Need the first bit instead? Switch to 1. Easy peasy, lemon squeezy (but make sure to split the lemon first).

Example:

Imagine you have a string like 'apple,banana,orange' and want just the banana part.

SELECT 
  SPLIT_PART
  (
  'apple
  ,banana
  ,orange'
  , ','
  , 2
  ) 
AS SecondPart
  ;

Result: “banana”

Trim That String Like a Bangs Refresh

Strings often come with messy edges, much like unkempt bangs in need of a snip. With TRIM, LTRIM, and RTRIM, you’ll be cutting away those pesky spaces or unnecessary characters in no time.

Side-Specific Styling:

Right trim:

SELECT 
  RTRIM
  (
  'Hello World   '
  ) 
AS RightTrimmed
;

Left trim:

SELECT 
  LTRIM
  (
  '   Hello World'
  ) 
  AS LeftTrimmed
;

Targeted Touch-Ups:

Remove specific characters, like overused emojis in a text.

SELECT 
  TRIM
    (
  BOTH 
    'x' 
  FROM 
    'xxxHello Worldxxx'
  ) 
AS CleanString
  ;

Result: “Hello World”

Full Makeover:

Remove all leading and trailing spaces.

SELECT 
  TRIM
  (
  '   Hello World   '
  ) AS 
CleanString
  ;

Result: “Hello World”

Snip and Clip with LEFT and RIGHT

For those moments when you just want a part of the string—like taking the first few letters of someone’s name for their initials, or the last digits of a card number to keep things mysterious.

RIGHT Function:

Snip off the end instead.

SELECT 
  RIGHT
  (
  'Hello World'
  , 5
  ) 
AS RightPart
  ;

Result: “World”

LEFT Function:

Like taking the first few pages of a book.

SELECT 
  LEFT
    (
    'Hello World'
    , 5
    ) 
    AS LeftPart
    ;

Result: “Hello”

REPLACE: The SQL Lipstick Eraser

Made a mistake? Want to swap something out? The REPLACE function is your SQL lipstick remover—erase and start fresh.

Example:

SELECT 
  REPLACE
    (
    'Hello World'
    , 'World'
    , 'Snowflake'
    ) 
    AS ReplacedString
    ;

Result: “Hello Snowflake”

For a bit more precision, there’s TRANSLATE, perfect for swapping individual characters like trading in heels for trainers after a long day.

The Art of the Substring

When you want just a slice of the pie—or string—go for SUBSTR() (or SUBSTRING() for the traditionalists).

Example:

Start at position 7 and grab 5 characters.

SELECT 
  SUBSTRING
    (
    'Hello World'
    , 7
    , 5
    ) 
  AS SubStringPart
    ;

Result: “World”

When SQL Needs to Slay the Case Game

Want to switch up the vibe of your text? These functions are like outfit changes for your strings:

INITCAP:

Title-case elegance.

SELECT 
  INITCAP
  (
  'hello world'
  ) 
AS TitleCase
  ;

Result: “Hello World”

LOWER:

Chill it out.

SELECT 
  LOWER
    (
    'HELLO WORLD'
    ) 
  AS LowerCase
  ;

Result: “hello world”

UPPER:

Make it loud and bold.

SELECT 
  UPPER
    (
    'hello world'
    ) 
  AS UpperCase
  ;

Result: “HELLO WORLD”

Clean Up Like a Pro

To finish, let’s talk about REGEXP_REPLACE for those advanced cleaning tasks, like removing non-alphanumeric characters from a string. It’s like a lint roller for your SQL data:

Example:

SELECT 
  REGEXP_REPLACE
    (
    'Hello@World#2025!'
    , '[^a-zA-Z0-9]'
    , ''
    ) 
  AS CleanString
    ;

Result: “HelloWorld2025”

And there you have it, babes: a beginner’s guide to cleaning up your SQL strings, the London way. It’s like turning chaotic data into a perfectly curated Instagram feed. Of course, there are countless other ways to glam up your strings, but this should get you started on your data prep journey.

As always, for more advanced tips, check out the Snowflake documentation: Snowflake Docs.

Until next time, keep your SQL as clean as your favourite pair of stilettos.

Song of the Day:

~S Xoxo