Have you ever come across a situation where you need to extract a specific portion of a string in SQL Server? In this article, we will discuss a common problem of extracting text between two delimiting characters and explore different methods to solve it.

The Problem

Imagine you have a table with a column called “Particulars” that contains a semi-colon-delimited string of text. Your task is to extract the text between the first and second semi-colon. However, there are a few challenges:

• The position of the first and second semi-colons is variable.
• The second semi-colon may not be present.
• The extracted text should be no longer than 99 characters.

Solution

One way to solve this problem is by using the SUBSTRING and CHARINDEX functions in SQL Server. Let’s take a look at the code:

SELECT Particulars,
   CASE
   -- when no second semi-colon,
   WHEN CHARINDEX(';',(SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99))) = 0
   -- then extract the full string from first semi-colon to the max length of 99
   THEN LTRIM(RTRIM(SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99)))
   -- else extract the full string from the first semi-colon up to second semi-colon
   ELSE LTRIM(RTRIM(SUBSTRING(
       /*  */particulars, 
       /*  */CHARINDEX(';',Particulars,1) + 1,
       /*  */CHARINDEX(';', SUBSTRING(Particulars, 
       CHARINDEX(';', Particulars, 1) + 1, 
     99)) - 1 
        ) ) )
   END AS Result_string
FROM Sample_table

This code uses the SUBSTRING function to extract the desired string value. The CHARINDEX function is used to identify the position of the semi-colons within the string. The start position is determined by the position of the first semi-colon plus one character. The length of the string is calculated by finding the position of the second semi-colon and subtracting one.

If there is no second semi-colon present, the code extracts the full string from the first semi-colon to a maximum length of 99 characters. Otherwise, it extracts the string between the first and second semi-colons.

Alternative Method

There are multiple ways to solve this problem. An alternative method is to use the LEFT, SUBSTRING, and CHARINDEX functions:

-- else extract the full string from the first semi-colon up to second semi-colon
ELSE RTRIM(LTRIM(LEFT(
   -- This is the text following the first semi-colon up to the max length of 99
   SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99),
   -- This is the length, one character before the next semi-colon
   CHARINDEX(';',SUBSTRING(Particulars,CHARINDEX(';',Particulars,1)+1,99)) - 1
   ) ) )

This code achieves the same result by using the LEFT function to extract the desired string. It then uses the SUBSTRING and CHARINDEX functions to determine the length of the string.

Summary

In this article, we have explored different methods to extract text between two delimiting characters in SQL Server. We have used the SUBSTRING, CHARINDEX, LTRIM, and RTRIM functions to accomplish this task. While there may be alternative solutions, these methods provide a straightforward approach without the need for complex cursors.

Next time you encounter a similar problem, you can refer back to this article and choose the method that suits your needs best.