How to split a text after a particular character with the SQL Server Substring function ? 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 point character must be done dynamically, i.e., whatever the position of the character is.
How to split a text after a predefined character with a SQL Substring query ?
First, to split a string with SQL Server just after a certain character, use the three following built-in text functions:
- SUBSTRING() to trim a text by indicating the start and length to be trimmed.
- 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.
- 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.
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 using SQL Server Substring function
-- 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'
Here is another article on text management in SQL, on how to manage strings with more than 8000 characters in T-SQL and specifically stored procedures.