Extracting a string from between two delimiting characters

(Part 2)

I recently received correspondence from Denis Oliynik, IT manager at JSC 'Rise', Kiev in the Ukraine, who kindly provided me with a script improving on my previously published solution.  Denis's solution removes the CASE statement for handling where a second delimiting character is not present.  This simplified code explained below offers a clever trick to add to your SQL skills.

Sample data

Here is the DML and DDL to create a sample table and insert some data

CREATE TABLE Sample_table (Particulars CHAR(120))
GO
INSERT INTO Sample_table VALUES('LDR ; LUC20031026901 ; Vehicle')
INSERT INTO Sample_table VALUES('LDR ; Consolidated')
INSERT INTO Sample_table VALUES('LDR ; SUB35030172701 ; Building')
INSERT INTO Sample_table VALUES('LDRR ; LIQ200310121 ; Liquor')

Solution

SELECT Particulars,
     LTRIM(RTRIM(SUBSTRING(
       /* <text>> */ Particulars,
       /* <start > */   CHARINDEX(';',Particulars,1) + 1,
       /* <length> */   CHARINDEX(';', 
          SUBSTRING(Particulars, CHARINDEX(';', Particulars, 1) + 1, 99) + ';') - 1 
      ) ) ) AS Result_string
FROM Sample_table

How it works

The problem previously was to identify the length from the first semi-colon to the second semi-colon.  If the second semi-colon was absent there was no reference point to work with and unless tested for by a prior CASE statement, the solution failed.  This solution overcomes the problem by adding in a semi-colon to the end of the string extracted by the SUBSTRING function.

In our examples the results of the above SUBSTRING line will look as follows

LUC20031026901 ; Vehicle;
Consolidated;
SUB35030172701 ; Building;
LIQ200310121 ; Liquor;

The original string for "Consolidated" did not have a semi-colon, which has now been added. This will be recognised by the second CHARINDEX to determine the end position.  The other three already had their second semi-colon, which the CHARINDEX will recognise.  In these instances the added semi-colon is not referenced.  Subtracting 1 from the result gives us the length less the semi-colon, and the resulting sting can be extracted.

Acknowledgment

Many thanks to Denis Oliynik, for showing that for every solution there is often a better one, and proving we are truly a global community. 

I hope the solution proves useful and feedback is welcomed.