How to split a string (with delimiter) using a single line query in SQL without the usage of user defined function?

Question

Scenario

The table structure of E_STProcedure

According to my existing scenario as shown in the above table STProcedure there could exists combination groups with respect to individual STProcedure. In the above picture it can be seen that the following relationships are true with STProcedureID and GroupID

A => A_B_C
B => B_A_E
C =>
D => D_B_C_E
E => D_E_A

Requirement + Initial Solution

It is required that the GroupIDs should be split with the delimiter '_' in a single line query.
I was able to find a solution by using a user defined function as follows

User defined function - fnSplit

ALTER FUNCTION  E1.fnSplit (@sInputList udtStringMax, @sDelimiter udtStringMax = '_') 
                                RETURNS @List TABLE (item udtStringMax,CNT INT IDENTITY(1,1))

BEGIN
    DECLARE @sItem udtStringMax
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    BEGIN
        SELECT
            @sItem=SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1),
            @sInputList=SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))

        IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
    END

    IF LEN(@sInputList) > 0
        INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
END

Single line query was as follows which used fnSplit

SELECT * FROM E1.E_STProcedure WHERE STProcedureID  IN  
    (SELECT item FROM E1.fnSplit((SELECT GroupID FROM E1.E_STProcedure WHERE STProcedureID='||OWNER||'),'_'))

where ||OWNER|| is the individual STProcedureID and E1 is the schema of the DB that I had

Limitations

Since the system was implemented in an MTMS (Multi-Tenant Multi-Schema) environment each query was schema dependant and had more than 100 schemas, the above code would only work for E1 schema.

I came to a conclusion that maybe there exists a workaround to split a string (with delimiter) using a single line query in SQL without the usage of user defined function, because the only other option is to replace and execute the query for each schema with its token.

Some of the answers to [Split string in SQL](http://stackoverflow.com/questions/2647/split-string-in-sql?rq=1) post show recursive (CTE-based) queries. — PM 77-1, Mar 13 '14 at 01:54
@PM77-1 I wanted to split the string using a single line query, as you know CTE is not single line. — Jerric Lyns John, Mar 13 '14 at 01:59
@PM77-1 _single line query_ can be any sql query which in the end can be executed in single line, In my question I've mentioned my initial implementation where the _single line query_ call as user defined function — Jerric Lyns John, Mar 13 '14 at 02:04
I'm still confused by your definition if "single line query." Your example has a line break. A query with a CTE can be flattened to a single (long, ugly) line since SQL does care much care about line-break. A quick Google suggests the preponderance of people use "single line query" to mean a query returning a single row. — Karl Kieninger, Mar 13 '14 at 14:56
@KarlKieninger My example has only a single line break, it doesn't really matter if the line breaks come in few numbers, but i believe if the __long and ugly__ CTE version can do the job then I'll be curiously waiting for an answer. — Jerric Lyns John, Mar 13 '14 at 16:24

score 1 · Answer 1 · edited Jul 29 '20 at 18:33

Alright, for the sake of argument, lets skip the CTE for now and instead do "long and ugly" with some xml. I'll leave your one line break in here.The basics of this are pulled from Brad Shulz by way of Aaron Bertrand. I changed from an int list, shortened a bit, took it out of a function, and put it on the one line.

SELECT * FROM E_STProcedure WHERE STProcedureID  IN  
    (SELECT Item FROM (SELECT Item = b.i.value('(./text())[1]', 'VARCHAR(MAX)') FROM (SELECT x = CONVERT(XML, '<i>' + REPLACE(Group_ID, '_', '</i><i>') + '</i>').query('.') FROM E_STProcedure WHERE STProcedureID='||OWNER||') a CROSS APPLY x.nodes('i') b(i)) c WHERE Item IS NOT NULL)

Given a few line breaks it reads better:

SELECT * 
  FROM E_STProcedure 
 WHERE STProcedureID IN (
       SELECT Item 
       FROM (SELECT Item = b.i.value('(./text())[1]', 'VARCHAR(MAX)') 
               FROM (SELECT x = CONVERT(XML, '<i>' + 
                                              REPLACE(Group_ID, '_', '</i><i>') +
                                             '</i>').query('.') 
                       FROM E_STProcedure 
                      WHERE STProcedureID='||OWNER||') a 
                    CROSS APPLY 
                    x.nodes('i') b(i)
                    ) c 
               WHERE Item IS NOT NULL)

Still could be done as a CTE, but I don't see a gain.

I appreciate your effort, but I have answered my own question with the help of my colleague, which is pretty readable. I'll get back to you answer ASAP, for now please do check my answer and comment on it. — Jerric Lyns John, Mar 17 '14 at 07:52

Jerric Lyns John · Accepted Answer · 2014-03-17T07:56:58.070

I was able to formulate the following solution with the help of my colleague

SELECT * FROM E1.E_STProcedure WHERE STProcedureID  IN  
    (SELECT ST2.STProcedureID   FROM E1.E_STProcedure ST1 
                                JOIN E1.E_STProcedure ST2 ON ST1.GroupID LIKE '%'+ST2.STProcedureID+'%' 
                                WHERE ST1.STProcedureID ='||OWNER||')

The Final Result

Explanation

WHERE clause

What happens here is that the WHERE clause will definitely cut down the result to a single record from ST1 relative to the respective GUID ||OWNER||
Lets simplify with the following example

WHERE ST1.STProcedureID ='17bfa492-8464-423f-a5f4-32978186e112'

The result of the WHERE clause

JOIN clause

The JOIN statement brings out the juice, The single entry from ST1.GroupID is checked for its containing sub-string from ST2.STProcedureID as shown below

E1.E_STProcedure ST1 
JOIN 
E1.E_STProcedure ST2 
ON 
ST1.GroupID LIKE '%'+ST2.STProcedureID+'%'

The result of the JOIN clause

I hope the code is pretty readable for you all.