Sample Header Ad - 728x90

SQL Server - Select where using split - without declaring function

2 votes
2 answers
3138 views
SQL Server. Compatibility level 120. I have a table where one VARCHAR column contains semicolon-separated integral values. I would like to SELECT against the values in this column as if they existed in a column of ints somewhere. Because of the compatibility level, I can't use STRING_SPLIT. I also cannot create any globally available functions. I can however create a temporary table if needed. What's my course of action? Some sort of nesting of WHILE loops (One for the rows of the table, another for the semicolons in the string)? update: This is perhaps a better explanation of what I want to achieve: Let's say I have the following tables.
CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);
CREATE TABLE Customer
(
	CustomerId int
	, CustomerName varchar(30)
);
With some data
INSERT INTO Customer (CustomerId, CustomerName)
VALUES 	(1, 'John')
	,(2, 'Niels')
	,(3, 'Frank')
	,(4, 'Barbie')
	,(5, 'Holly')
	,(6, 'Ebeneezer');
INSERT INTO Service (ServiceId, CustomerIds)
VALUES	(1, '4')
	,(2, '2;3');
And let's say I want to select Customer.CustomerName for any Customer who's ID shows up in the CustomerIds in any line in that table. That is, I want to retrieve Niels, Frank and Barbie.
Asked by Christofer Ohlsson (131 rep)
Dec 6, 2019, 03:46 PM
Last activity: Dec 9, 2019, 10:20 AM