Nov 2, 2015 · You can't do this with just the native STRING_SPLIT function added in SQL Server 2016, because there is no guarantee that the output will be rendered in the order of the original list. In other words, if you pass in 3,6,1 the result will likely be in that order, but it could be 1,3,6 .

This is most like .NET, for those of you who are familiar with that function: CREATE FUNCTION dbo.[String.Split] ( @Text VARCHAR(MAX), @Delimiter VARCHAR(100), @Index INT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX)); DECLARE @R VARCHAR(MAX); WITH CTE AS ( SELECT 0 A, 1 B UNION ALL SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter ...

if the values in column 1 are always one character long, and the values in column 2 are always 2, you can use the SQL Left and SQL Right functions: SELECT LEFT(data, 1) col1, RIGHT(data, 2) col2 FROM <table_name>

May 14, 2012 · You can use a table-valued function STRING_SPLIT, which is available only under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute the STRING_SPLIT function. You can change a compatibility level of the database using the following command:

Jun 6, 2012 · Finally the wait is over in SQL Server 2016 they have introduced Split string function : STRING_SPLIT. select * From STRING_SPLIT ('a,b', ',') cs All the other methods to split string like XML, Tally table, while loop, etc.. has been blown away by this STRING_SPLIT function.

Here is a simple function that could be used to split string in DB: SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); it will return all the values by splitting with space.

Jul 30, 2012 · I wish to use the SQL "split" function: alter FUNCTION [dbo].[Split3] (@String nvarchar(1000), @Delimiter char(1)) returns @temptable TABLE (items nvarchar(1000)) as ...

Oct 9, 2015 · From SQL SERVER 2016 we can use sql inbuilt function STRING_SPLIT as below : SELECT * FROM JobOffers as j outer apply STRING_SPLIT(j.[Categories], ',') s left join dbo.Categories as c on c.CategoryID =s.value

Dec 3, 2019 · There are far better ways to split a string, such as an XML Splitter (Splitting Delimited Strings Using XML in SQL Server), A Tally Splitter (DelimitedSplit8k_LEAD), CLR functions, JSON String splitters and the in built STRING_SPLIT function. a WHILE is a looong way down the list as a option.

Mar 13, 2015 · I am trying to create a function in SQL Server to split the input string and return a part of the string based on the delimiter's position.. For instance. I have a string . str = kool_2214_live_dbp123 And if I parse the text, delimiter and the position of the delimiter. I would like to get the value after that delimiter