Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alternative for String_split in SQL Server

Tags:

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.

like image 331
user3715927 Avatar asked Jun 28 '18 19:06

user3715927


People also ask

How do I separate two words in SQL?

The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.

How split comma-separated values in SQL Server?

Split comma-separated value string in a column. SELECT ProductId, Name, value FROM Product CROSS APPLY STRING_SPLIT(Tags, ','); Here is the result set. The order of the output may vary as the order is not guaranteed to match the order of the substrings in the input string.


1 Answers

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'
like image 147
John Cappelletti Avatar answered Nov 12 '22 22:11

John Cappelletti