December 12, 2017 at 8:24 am
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
December 12, 2017 at 9:14 am
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
December 12, 2017 at 9:39 am
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),'|'), '|')
December 12, 2017 at 9:44 am
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
December 17, 2017 at 4:55 pm
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 .
December 17, 2017 at 8:48 pm
Heh... I have a much easier solution. Use the DelimitedSplit8K function for this and not worry about stuff breaking.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2017 at 2:50 am
Jeff Moden - Sunday, December 17, 2017 8:48 PMHeh... I have a much easier solution. Use the DelimitedSplit8K function for this and not worry about stuff breaking.
+100
...
January 3, 2018 at 9:05 am
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 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy