With the release of SQL Server 2016 RC0, comes a function I have waited a long time for the string_split function. Everyone has there own different way of splitting strings, and I shall walk you through how I used to this, and how I will now be splitting strings using SQL Server 2016 RC0 onwards.
Take the following example where I have one record which contains 2 columns, my 2nd column containing multiple values which I want to split out.
Previously I would have used some string manipulation and the xml data type to split the values into column 2 out as separate values using code like that shown below.
;WITH CTE AS (SELECT UPPER('Adatis') as Brand_Code ,CAST ('' + REPLACE('1,2,3,4,5,6', ',', '') + '' AS XML) AS Ord ) SELECT Cte.Brand_Code, Ord.a.value('.', 'VARCHAR(100)') as OrderID FROM CTE OUTER APPLY Ord.nodes ('/M') AS Ord(a) --Use the node method to join back to the original data
Now all I have to do is call the SPLIT_STRING function in SQL Server 2016 simply specifying the string to be split and the value to split by.
SELECT UPPER('Adatis') as Brand_Code, Value FROM string_split('1,2,3,4,5,6',',')
This works fine if you just want to split a single string, but if you have multiple values you want to split then you will need to use CROSS APPLY.
Given the table below with two columns which replicate the query above.
I would now need to only write the following query which is a lot neater and easier for anyone reviewing my work to understand. Here I am simply asking it to split out values found in the OrderID column using a comma as the value to split by.
SELECT BrandCode, Value FROM StringSplitExample a CROSS APPLY STRING_SPLIT(a.OrderID,',')
The Results:
One thing to note, is that this function will not strip out any leading/trailing spaces so this will have to be handled either by using RTRIM and LTRIM or a REPLACE function. The following screenshots show the issue and my resolution
Before:
SELECT BrandCode, LTRIM(RTRIM(Value)) FROM StringSplitExample a CROSS APPLY STRING_SPLIT(a.OrderID,',') WHERE BrandCode = 'Other2'
After:
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr