{"id":6960,"date":"2024-03-12T07:35:37","date_gmt":"2024-03-12T06:35:37","guid":{"rendered":"https:\/\/expert-only.com\/?p=6960"},"modified":"2024-03-12T11:40:27","modified_gmt":"2024-03-12T10:40:27","slug":"split-a-string-after-a-character","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/split-a-string-after-a-character\/","title":{"rendered":"How to Split a String after a Character with SQL Server ?"},"content":{"rendered":"\n

How to split a simple string stored in a variable after a specific character using SQL Server functions?<\/em><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

1. Split a string after a character using 3 standard T-SQL functions<\/h2>\n\n\n\n

First, to split a string with SQL Server just after a certain character, use the three following built-in text functions:<\/p>\n\n\n\n

    \n
  1. SUBSTRING<\/strong> to trim a text by indicating the start and length to be trimmed.<\/li>\n\n\n\n
  2. CHARINDEX<\/strong> 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.<\/li>\n\n\n\n
  3. LEN<\/strong> is a function that returns the length of a given text.<\/li>\n<\/ol>\n\n\n\n

    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<\/a> (like dots, semi-columns or commas) with a simple T-SQL script.<\/p>\n\n\n\n

    2. T-SQL query to cut the text using 3 variables<\/h2>\n\n\n\n
      \n
    1. StartString <\/strong>: to store the complete string to process.<\/li>\n\n\n\n
    2. Result <\/strong>: to store the result of the string functions.<\/li>\n\n\n\n
    3. Character <\/strong>: the delimiter to search inside the text.<\/li>\n<\/ol>\n\n\n\n

      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<\/a> function.<\/p>\n\n\n\n

      -- Declaration of variables used as start string, result string\n-- and the character used to split the text\ndeclare @StartString nvarchar(100)\ndeclare @Result nvarchar(100)\ndeclare @Character nvarchar(10)\n\n-- Set the string, for example 'file.ext' \nset @StartString = 'START_STRING.END_STRING'\n\n-- Set the character or string to search for to split the string in two\nset @Character = '.'\nset @Result =\n  substring(@StartString,CHARINDEX(@Character, @StartString)+1,len(@StartString))\n\nselect @StartString as 'Start String'\nselect @Result as 'Result String'\n<\/pre>\n\n\n
      \n
      \"How
      How to Split a String after a Character with SQL Server ?<\/em><\/figcaption><\/figure><\/div>\n\n\n
      Splitting SQL strings without the SPLIT_STRING function<\/h3>\n\n\n\n
      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<\/a> and specifically inside stored procedures.<\/p>\n\n\n\n
      \n
      SQL Server string longer than 8000 characters<\/a><\/blockquote>