It is a very common case when one needs to remove values left or right of a delimiter.
The pattern below should work for most of the common cases.
Note that JSON was misinterpreting some special characters, therefore, I posted a screenshot of the code to build a temporary table. The rest of the code you can copy and paste.
// Test values //
SELECT * FROM #Delimiter
The very useful CHARINDEX function locates the numeric place of the index within the string.
In the example above it is position 3. Then, the LEFT function picks up a character left of 3.
However, since we do not need the actual delimiter, we subtract 1 from its position.
Therefore, LEFT picks up the first 2 characters.
// Values left of the delimiter //
SELECT LEFT(String, CHARINDEX(‘-‘, String)-1) AS LeftofDelimiter FROM #Delimiter
LEN function allows us to derive the length of the whose string, then we subtract the location of the delimiter from the length of the string. After that, we take that difference (e.g. 8 characters) from the right side of the string. The last step is to remove any spaces from the right and left sides. It is always a good practice that prevents potential data issues down the road.
// Values right of the delimiter //
SELECT RTRIM(LTRIM(RIGHT(String, LEN(String) – CHARINDEX(‘-‘, String)))) AS RightofDelimiter FROM #Delimiter
