Split a string after a character with SQL Server

How to split a string after a particular character with the SQL substring function?

For example, use the substring function to split a string like file.txt to separate the file name from its extension after the dot character. Indeed, it is obvious that splitting a string just before the point character must be done dynamically, i.e., whatever the position of the character is.

How to split a string after a character with the substring function?

First, to split a string with SQL Server just after a certain character, use the three following built-in text functions:

  1. SUBSTRING to trim a text by indicating the start and length to be trimmed.
  2. CHARINDEX returns the position of a text to search. The function uses the text to search for, the text to search in and the starting position of the search.
  3. LEN is a function that returns the length of a given text.

By the way, this example is not recursive. However, to make it recursive, integrate the logic in a loop. Then adjust the variable that contains the character in the main string. This time, here is an example of how to recursively split a string that contains delimiters (like dots, semi-columns or commas) with a simple T-SQL script.

Build the T-SQL code with 3 variables

  1. StartString : to store the complete string to process.
  2. Result : to store the result of the string functions.
  3. Character : the delimiter to search inside the text.

To adapt it to your coding case and make your SQL query work, simply change the @Character variable. And of course, also change the assigned value in the @StartString T-SQL variable. Example of T-SQL code to split a text after a specific character using SQL Server substring function.

-- Declaration of variables used as start string, result string
-- and the character used to split the text
declare @StartString nvarchar(100)
declare @Result nvarchar(100)
declare @Character nvarchar(10)

-- Set the string, for example 'file.ext' 
set @StartString = 'START_STRING.END_STRING'

-- Set the character or string to search for to split the string in two
set @Character = '.'
set @Result =
  substring(@StartString,CHARINDEX(@Character, @StartString)+1,len(@StartString))

select @StartString as 'Start String'
select @Result as 'Result String'
Query executed in SSMS to split a string after a character
Query executed in SSMS to split a string after a character

In the screenshot of the query, the last part of the text is isolated by the substring function and displayed as a result. Here is another SQL tutorial on text management, learn to manage strings with more than 8000 characters with T-SQL variables and specifically inside stored procedures.

https://expert-only.com/en/t-sql/sql-server-text-with-more-than-8000-characters/

Leave a Comment

Your email address will not be published. Required fields are marked *