Extracting a string from between two delimiting characters
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.
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')
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.
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.