STRING_SPLIT with no delimiter
-
if it helps:
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
us_english
Default collation SQL_Latin1_General_CP1_CI_AS
-
I've tried it on 2016SP1CU3, 2016SP1CU5, 2017CU2, all with default collation of Latin1_General_CI_AI, and also only got the single row...
Thomas Rushton
blog: https://thelonedba.wordpress.com -
I found this solution interesting, but I was especially intrigued by the inconsistent results. So I did my own testing by breaking down the solution.
I suspect the reason some people are getting only a single A as result is not due to the version of SQL, but the version of Management Studio.
The solution is based on the introduction of the Char(0) character as a delimiter, and I think this specific character is playing havoc with Management studio.
Hers is my findings and I would be curious to know if the posters who had issues could verify if this would work for them.DECLARE@A VARCHAR(6) = 'ABCDEF'
Select CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR), Len(CAST(CAST(CAST(@AAS NVARCHAR) AS VARBINARY) AS VARCHAR))
-- You have a 12 character string but only 1 is displayed
-- For some reason the ASCII NUL(Char(0)) introduced from the data type changes, iscausing the whole string to not be displayed.
-- I suspect this is a Management Studio limitation not SQL's handling of the data (What You See Is NOT What you Got)-- Replace the ASCII NUL character and the result displays as expected
Select Replace(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR),Char(0),'|')
-- Modified SQL that should work regardless of Management Studio version. With theexception of the dangling delimiter that will create an extra empty row in theresulting table.
SELECT value FROM STRING_SPLIT(Replace(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) ASVARCHAR),Char(0),'|'), '|')
-
I've just reproduced the problem with SQLCMD... 😀 - that just returns "A"
Not got SSMS 17.4; got the problem when running with SSMS 17.3.
The full string ("A B C D E F") appears when returning th eresults into a text page, but only "A" is returned in grid results.
Thomas Rushton
blog: https://thelonedba.wordpress.com -
OK. This is definitely not due to versions of SQL Server. I've tested all CUs of SQL Server 2016, and several updates for SP1, and I cannot make it fail on my PC:
My guess is that this is due to either Windows regionalization/code page settings or .NET versions, and I'm not ready to test those things.
Here are the results of my tests, combined with results from other forum posters. Note I covered all versions posted as failed except for KB4019088 -- I had already updated CU1 before I noticed I missed that one.Version String Major Minor From Test Results Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 1601 2016 RTM ISO Successful (NOT PROVIDED) 13 1601 Forum: Evgeny Failed Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 14393: ) 13 1742 KB4019088 - Forum: J Livingston SQL Failed Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64) Jul 11 2016 22:05:22 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2149 kb3164674 Successful Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) - 13.0.2164.0 (X64) Sep 9 2016 20:13:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2164 KB3182270 Successful Microsoft SQL Server 2016 (RTM-CU3-GDR) (KB3194717) - 13.0.2186.6 (X64) Oct 31 2016 18:27:32 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2186 KB3194717 Successful Microsoft SQL Server 2016 (RTM-CU4) (KB3205052) - 13.0.2193.0 (X64) Jan 6 2017 11:59:15 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2193 KB3205052 Successful Microsoft SQL Server 2016 (RTM-CU5) (KB4013105) - 13.0.2197.0 (X64) Feb 25 2017 12:07:22 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2197 KB4013105 Successful Microsoft SQL Server 2016 (RTM-CU6) (KB4019914) - 13.0.2204.0 (X64) Apr 20 2017 23:59:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2204 KB4019914 Successful Microsoft SQL Server 2016 (RTM-CU7) (KB4024304) - 13.0.2210.0 (X64) Jul 16 2017 17:56:09 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2210 KB4024304 Successful Microsoft SQL Server 2016 (RTM-CU8) (KB4040713) - 13.0.2213.0 (X64) Sep 5 2017 15:22:54 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 2213 KB4040713 Successful Microsoft SQL Server 2016 (RTM-CU9) (KB4037357) - 13.0.2216.0 (X64) Nov 9 2017 14:24:19 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 15063: ) (Hypervisor) 13 2216 KB4037357 Successful Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 4001 KB3182545 Successful Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 14393: ) 13 4206 KB4019089 - Forum: J Livingston SQL Failed Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64) Jul 6 2017 07:55:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 4206 KB4019089 Successful (NOT PROVIDED) 13 4435 Forum: Evgeny Failed Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64) Jul 16 2017 18:08:49 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: ) (Hypervisor) 13 4446 KB4024305 Successful Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 15063: ) (Hypervisor) 14 1000 2017 RTM Successful For reference, you can get all SQL 2016 updates from http://www.catalog.update.microsoft.com/search.aspx?q=sql+server+2016 .
-
Heh... I have a much easier solution. Use the DelimitedSplit8K function for this and not worry about stuff breaking. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Sunday, December 17, 2017 8:48 PM
+100
...
-
I only get the A in the grid, but A B C D E F in text result. Strange.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply