string functions

SQL Server SUBSTRING(): A Complete Guide to Extracting Strings

by

The SUBSTRING() function in SQL Server lets you pull out specific portions of a string. Whether you’re cleaning data, formatting output, or parsing complex text fields, this function is one you’ll use constantly.

If you’ve ever needed to extract an area code from a phone number, grab the domain from an email address, or parse product codes into their component parts, SUBSTRING() is your go-to tool. It’s pretty straightforward, and once you understand how it works, you’ll find yourself using it all the time.

Read more

Detecting and Filtering Special Characters Using PATINDEX() and LIKE in SQL Server

by

Working with real-world data often means dealing with messy strings. It’s common to find values that contain unexpected special characters. Sometimes this is due to user input, sometimes it’s from imports or third-party sources.

Either way, when we need to find and filter these special characters, SQL Server gives us some handy tools to work with. For starters, there’s the LIKE operator, which anyone who’s used SQL would be familiar with. But there’s also the PATINDEX() function, which performs a slightly different task.

Read more

Simple CONCAT() Usage vs Manual String Building in SQL Server

by

When working with SQL Server, string concatenation is one of those everyday tasks that’s easy to take for granted. It can feel like second nature to reach for the trusty old + operator to piece together strings, but SQL Server also provides an alternative way to handle concatenations. Yes, I’m referring to the CONCAT() function.

And there’s a subtle difference between the two approaches that might sway you towards using one or the other.

Let’s compare these two approaches to building strings in SQL Server.

Read more

Split and Re-Aggregate Delimited Strings in SQL Server

by

Working with delimited strings in SQL Server can be messy. Maybe you inherited a table where a column holds multiple values separated by commas, or you need to take a list and break it apart before putting it back together in some aggregated form. While best practice is to normalize data into proper relational tables, sometimes you don’t control the schema. Or perhaps you’re just solving a one-off reporting need.

This article walks through how to split delimited strings into rows, process them, and then re-aggregate them back into a single string, all within SQL Server.

Read more

How to Replace INSTR() with CHARINDEX() and PATINDEX() in SQL Server

by

If you’ve worked with Oracle or MySQL before, you may have used the INSTR() function to find the position of a substring inside a string. But when you’re porting code to SQL Server, you’ll quickly notice that INSTR() isn’t available. Instead, you’ll need to use either CHARINDEX() or PATINDEX(), depending on what you’re trying to do.

The good news is that both functions are pretty straightforward once you know the difference. CHARINDEX() handles simple substring searches, while PATINDEX() adds the ability to use patterns. In this article, we’ll walk through how each one works and how you can swap them in when you’d otherwise use INSTR().

Read more

How to Use STRING_AGG() in SQL Server with Custom Separators and Sorting

by

In SQL Server, STRING_AGG() is an aggregate function that concatenates string values from a group into a single string. It’s a handy tool for doing things like creating comma-separated lists from related data.

In this article we’ll check out how to use STRING_AGG() with different separators. We’ll also see how we can control the order of the concatenated strings.

Read more

RPAD() Alternative: Applying Right Padding in SQL Server

by

The SQL rpad() function has been widely implemented across many major RDBMSs, including MySQL, Oracle, PostgreSQL, and MariaDB, to name just a few. But when it comes to SQL Server, we have a problem. SQL Server doesn’t currently provide us with an rpad() function.

But that’s not to say we can’t apply right padding in SQL Server. SQL Server still provides us with enough tools to get the job done. With a bit of work, we can get a similar result to what we might be able to achieve with rpad(). It may not be as elegant as a simple rpad() function, but at least it’s an option.

Read more

How CONCAT_WS() Works in DuckDB

by

In DuckDB, the CONCAT_WS() function provides an efficient way to join strings with a specified separator. CONCAT_WS() stands for “concatenate with separator”, and many RDBMSs have such a function.

CONCAT_WS() is particularly useful when you need to combine multiple fields or values with a consistent delimiter.

Let’s explore its features and practical applications.

Read more

An Overview of the CONCAT() Function in DuckDB

by

String concatenation is a common operation in database queries, and many database management systems (DBMSs) provide at least one or two ways to concatenate strings. DuckDB is no exception.

One option for concatenating strings in DuckDB is with the CONCAT() function. This function provides a robust and NULL-safe way to combine strings.

Unlike the concatenation operator (||), CONCAT() handles NULL values gracefully and provides a cleaner syntax for combining multiple strings.

In this article, we’ll explore everything from basic usage to advanced techniques and best practices.

Read more