Sql-server – SQL Server – Select where using split – without declaring function

sql serverstring-splittingt-sql

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.

Best Answer

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(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') 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(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') 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(('<X>' + replace(CustomerIds, ';', '</X><X>') + '</X>') 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        |
Related Question