String splitting

Transact-SQL
1

Hello,

I have a table (table1) that contains a long string which is divided into two parts separated by a two-character delimiter. I need to split the string and keep only one half. The half to be kept will be the half that contains a string that is listed in another table (table2). How can this be done in a single statement?

Thank you

2

See: String Splitter

3

If you just want to slice a string into two based on a two-character delimiter (which exists only once in the string), you can do that like this:

DECLARE @delimiter CHAR(2) = 'XY';
DECLARE @string VARCHAR(1024) = 'ABCDEFXY12345';
SELECT LEFT(@string, CHARINDEX(@delimiter,@string+@delimiter)-1),
	STUFF(@string,1,CHARINDEX(@delimiter,@string+@delimiter)+1,'')

To use that same logic in your Table1 and Table2 and pick out whichever is listed in Table2,

DECLARE @delimiter CHAR(2) = 'XY';
SELECT
	t2.LookupValue
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t2.LookupValue = LEFT(ColToSplit, CHARINDEX(@delimiter,ColToSplit+@delimiter)-1)
		OR t2.LookupValue = STUFF(ColToSplit,1,CHARINDEX(@delimiter,ColToSplit+@delimiter)+1,'')

This of course, is something for you to start with. It makes a few assumptions - for example, that both values are not listed in the table2, and that a given value is listed only once in table2 etc.