SQL Server - Select where using split - without declaring function
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        |