Cut text after a character with SQL Server

How to split a text after a particular character with SQL Server… like for example a file name: “file.txt” to separate the file name from its extension. Indeed, it is obvious that splitting a string just before the “.” character must be done dynamically, i.e., whatever the position of the character.

How to split a text after a predefined character with an SQL query?

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

  1. SUBSTRING()
  2. CHARINDEX()
  3. LEN()

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 caracter in the main string.

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 SQL code to split a text after a specific character.

-- Declaration of variables used as start string, result string and the character used
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'

This time, here is an example of how to recursively split a string that contains delimiters (like dots, semi-columns or commas) in SQL Server in T-SQL.