This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.
The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.
/*Delimiter variables, first and second position*/ DECLARE @dfp AS CHAR(1); DECLARE @dsp AS CHAR(1); DECLARE @text VARCHAR(MAX); SET @dfp = ';'; SET @dsp = '@'; SET @text = 'I want you to ;Extract this@ substring for me please.'; SELECT SUBSTRING(@text, (CHARINDEX(@dfp, @text) + 1), (CHARINDEX(@dsp, @text) - 2) - CHARINDEX(@dfp, @text) + Len(@dsp))