Introduction:
String manipulation and JSON handling are fundamental skills in SQL Server, empowering you to process and analyze text and structured data efficiently. Whether you’re splitting and concatenating strings, trimming whitespace, or working with JSON data, SQL Server offers a robust set of functions to tackle these tasks. In this post, we’ll explore key functions like STRING_SPLIT
, STRING_AGG
, SUBSTRING
, CHARINDEX
, PATINDEX
, LTRIM
, RTRIM
, TRIM
, LEFT
, RIGHT
, STUFF
, Format and essential JSON functions to enhance your SQL skills.
What You’ll Learn:
By the end of this post, you’ll be able to:
- Split a string into multiple rows with
STRING_SPLIT
. - Concatenate multiple rows into a single string using
STRING_AGG
. - Extract and manipulate substrings using
SUBSTRING
,LEFT
, andRIGHT
. - Locate specific characters or patterns with
CHARINDEX
andPATINDEX
. - Clean up strings with
LTRIM
,RTRIM
, andTRIM
. - Modify strings by inserting or deleting characters with
STUFF
. - Handle and query JSON data using
FOR JSON PATH
,JSON_VALUE()
, andJSON_MODIFY()
.
1. STRING_SPLIT
Purpose: Splits a string into rows based on a specified delimiter.
Syntax:
STRING_SPLIT(string, delimiter)
Example:
SELECT value
FROM STRING_SPLIT('Apple,Banana,Cherry', ',');
Use Case: Parsing comma-separated values into individual rows.
2. STRING_AGG
Purpose: Concatenates values from multiple rows into a single string with a specified delimiter.
Syntax:
STRING_AGG(expression, delimiter)
Example:
SELECT STRING_AGG(FirstName, ', ') AS EmployeeNames
FROM Employees;
Use Case: Generating a comma-separated list of names.
3. SUBSTRING
Purpose: Extracts a portion of a string starting at a specified position.
Syntax:
SUBSTRING(expression, start, length)
Example:
SELECT SUBSTRING('Hello World', 1, 5) AS SubstringResult;
Use Case: Extracting specific parts of a string.
4. CHARINDEX
Purpose: Returns the starting position of a specified substring within a string.
Syntax:
CHARINDEX(substring, string)
Example:
SELECT CHARINDEX('World', 'Hello World') AS Position;
Use Case: Finding the position of a substring within a string.
5. PATINDEX
Purpose: Returns the starting position of a pattern within a string.
Syntax:
PATINDEX('%pattern%', string)
Example:
SELECT PATINDEX('%Wo%', 'Hello World') AS Position;
Use Case: Locating patterns within strings, including wildcard searches.
6. LTRIM / RTRIM / TRIM
Purpose: Remove leading/trailing or both leading and trailing spaces from a string.
Syntax:
LTRIM(string) -- Removes leading spaces
RTRIM(string) -- Removes trailing spaces
TRIM(string) -- Removes both leading and trailing spaces
Example:
SELECT RTRIM(' Hello World ') AS RTrimmedString;
SELECT LTRIM(' Hello World ') AS LTrimmedString;
SELECT TRIM(' Hello World ') AS TrimmedString;
Use Case: Cleaning up whitespace in data entries.
7. LEFT / RIGHT
Purpose: Extract characters from the left or right side of a string.
Syntax:
LEFT(string, length) -- Extracts from the left
RIGHT(string, length) -- Extracts from the right
Example:
SELECT LEFT('Hello World', 5) AS LeftString;
SELECT RIGHT('Hello World', 5) AS RightString;
Use Case: Extracting fixed-length substrings from either end of a string.
8. STUFF
Purpose: Deletes a specified length of characters and inserts another set of characters at a specified position.
Syntax:
STUFF(string, start, length, new_string)
Example:
SELECT STUFF('Hello World', 6, 5, 'SQL') AS StuffedString;
Use Case: Replacing part of a string with new content.
9. JSON Handling Functions
FOR JSON PATH
Purpose: Converts the result set of a SQL query into JSON format.
Syntax:
SELECT columns
FROM table_name
FOR JSON PATH;
Example:
SELECT FirstName, LastName
FROM Employees
FOR JSON PATH;
Use Case: Generating JSON data for web APIs or data export.
JSON_VALUE()
Purpose: Updates the value of a property in a JSON string.
Syntax:
JSON_MODIFY(json_column, '$.property', new_value)
Example:
SELECT JSON_VALUE(EmployeeData, '$.FirstName') AS FirstName
FROM EmployeeRecords;
Use Case: Extracting specific values from JSON data.
JSON_MODIFY()
Purpose: Updates the value of a property in a JSON string.
Syntax:
JSON_MODIFY(json_column, '$.property', new_value)
Example:
UPDATE EmployeeRecords
SET EmployeeData = JSON_MODIFY(EmployeeData, '$.Salary', 70000)
WHERE JSON_VALUE(EmployeeData, '$.FirstName') = 'John';
Use Case: Modifying JSON data directly within a database.
10. Format
Purpose: is used to format dates, times, and numbers according to a specified format string.
Syntax:
FORMAT(value, format_string [, culture])
Example:
SELECT FORMAT(1234.5678, 'N2') AS FormattedNumber;
Use Case: Date Formatting, Number Formatting
Feel free to try out these functions in your SQL Server environment and share your experiences or any questions in the comments section. If you have any additional tips or advanced use cases for these functions, we’d love to hear about them! Don’t forget to subscribe for more in-depth SQL Server tutorials and tips.
Thanks for reading, and happy querying!