CODEWITHSUNDEEP
sql-server
user3715927
user3715927

Reputation: 113

Alternative for String_split in SQL Server

WITH dataforIDs AS
(
    SELECT 
        value, ERSBusinessLogic_InputDataSeries
    FROM 
        [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]
    CROSS APPLY 
        STRING_SPLIT(ERSBusinessLogic_InputDataSeries, ',')
    WHERE 
        ERSBusinessLogic_InputGeographyDimensionID = 7493
        AND ERSBusinessLogic_InputTimeDimensionValue = 'all months'
        AND ERSBusinessLogic_Type = 'HS10 aggregation'
 )

Now I use this query to split the column ERSBusinessLogic_InputDataSeries which is comma separated, but I need to set the compatibility_level to 140. I need an alternative as this will run on the server and I cannot change it explicitly there.

Upvotes: 10

Views: 14647

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

If by chance you can't use a TVF

The TVF that scsimon linked to is very performant. The XML approach is a close second.

Example

SELECT value,ERSBusinessLogic_InputDataSeries
FROM  [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]
Cross Apply (
                Select Seq   = Row_Number() over (Order By (Select null))
                      ,Value = v.value('(./text())[1]', 'varchar(max)')
                 From  (values (convert(xml,'<x>' + replace(ERSBusinessLogic_InputDataSeries,',','</x><x>')+'</x>'))) x(n)
                 Cross Apply n.nodes('x') node(v)
              ) B
where ERSBusinessLogic_InputGeographyDimensionID = 7493
  and ERSBusinessLogic_InputTimeDimensionValue = 'all months'
  and ERSBusinessLogic_Type = 'HS10 aggregation'

Upvotes: 13

Related Questions