SQL Server - Select where using split - without declaring function 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.
A possible solution might be Splitting Delimited Strings Using XML in SQL Server.  From that post:


  Problem
  
  This article will help developers looking for a way to split delimited
  strings in a single query using XML. We generally use a user defined
  function to do this, which you have probably found in many places that
  splits the string based on the delimiter passed. But, when it comes to
  separating the string in a single query without any help of a user
  defined function there are not many options. I have found a much
  simpler and shorter way of splitting any string based on a delimiter.
  I will be using the power of XML to do the splitting of the string
  instead of a user defined function.
  
  Solution
  
  Let's say for example there is a string 'A,B,C,D,E' and I want to
  split it based on the delimiter ','.
  
  The first step would be to convert that string into XML and replace
  the delimiter with some start and end XML tags.


DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('&lt;X&gt;'+replace(@str,@delimiter ,'&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;') as xml)
SELECT @xml


Once the string is converted into XML you can easily query that using XQuery

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('&lt;X&gt;'+replace(@str,@delimiter ,'&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)




| value |
|-------|
| A     |
| B     |
| C     |
| D     |
| E     |




UPDATE 2019-12-09

Based on your updated question, here is a sample solution

set nocount on;
drop table if exists Service;
CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);
drop table if exists Customer;
CREATE TABLE Customer
(
    CustomerId int
    , CustomerName varchar(30)
);
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');
-----------------------
;WITH ServicesAsXml
AS (
    SELECT ServiceId
        ,cast(('&lt;X&gt;' + replace(CustomerIds, ';', '&lt;/X&gt;&lt;X&gt;') + '&lt;/X&gt;') AS XML) AS xmlcol
    FROM Service
    )
    ,ServicesSplit
AS (
    SELECT ServiceId
        ,T.N.value('.', 'varchar(10)') AS CustomerId
    FROM ServicesAsXml
    CROSS APPLY xmlcol.nodes('X') T(N)
    )
SELECT CustomerName
FROM Customer c
JOIN ServicesSplit ss ON ss.CustomerId = c.CustomerId




| CustomerName |
|--------------|
| Barbie       |
| Niels        |
| Frank        |