STRING_SPLIT with no delimiter

  • Toreador

    SSChampion

    Points: 11261

    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
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEwAAACZCAYAAACFZNy3AAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAbASURBVHhe7ZxdaBRXGIbfSRF/ij/QiwqWtiRZwXV7WzS5s21MDJQYS3pVQolUqEIiiAh6VYQWCWLUXihIkd7FxFrFTRNa6Y02SKFQsys0u+hFW3pTWE2z5m93e87kzDqbbLP7TU5n5qzfk3yZOXN2Jmff+c6ZM28ma2UymQKYqrGuDd9iwQhYBYFaXzVTU1PYuHGjKoUfL+2tU0umSrQKpjFZfcFLeznDiIRPsNQAmpsHkFLFsMFdkggLRqRuZnYeOiKXy4vlHBYWcqV1ybPYbTWhP7m8/O1BC5b1PA7eFPVzOeTzOczK1+0+i4T7OE7ZPoazn+vYhHDaS6VOqqwjJPl8HnKtpK7+CE70/ITheMoup+LXMN5zAofrC2i5OIvsMxUT/Uh8fh4p+1jOMV3HKZZTuNBzDZ0Tzn4HMNyzuJ/7tZVCIttLRVuXFCe72JCltBzth1AMafE1MgycOdq6WJG+gD3r12KDjNgxjC9uXZl0HNfHx3E85tpvfAgjaVVfJSu1dyW0ZZh9/tWPZXX1beiEeFOj4s3iA7SK7CoUvsOR2BA6H8xgOiviQT92OccoWTrHccryZw++kfsU40c7Y0t+Z6UoHouGT9OKBrR2Asc7jgGdbaLksBMRp5B+WJphDRFExx+KnFQ49Q1C/F1XcGvM3iq2X8Q7Gw7DKf7f6BvDxHfZMUxFfesBvC1yaH9rvdrWgr4zCezfsA4vy+i4Ive0T7t9lKX1XyTE/nJ7PT794QbQoba/NYT9v17Ee67fVU047aViTf2TFbuunvXr1uL3P/7E1q2vYn5+QW0NL05733j9NbWlOjReJdVSfi+pC2MU20tE86Avl65tYQ7VXipWIpGg71VDRKNRtVYd1vz8vDbB5BmTs29TkO1ds2aNKlVH3fT0NHRFNpu1GyDXTVjKoKLVon7y5AlmcuFzjMqx7qU8Nm/erErVo/3dbX1lk1oLN1u2bFFrNLQL9tffT9VauMlkMmqNBmcYkWAy7PteIey7uPxIlQPAqAy7E/8KH3XvxM1RoiejEYMyLI1U8mO0nW3HjhujeKy2+o3nDCtn33oNScUMezSKm9F27EEDIriOsYC6pecMk9MwXSGplGGPR69jx74WsdaAlg4E1i0NGcPSGLtxH19/uMl+3a6T9/FzQN3SjAyzu+OQ/ZrF+AWfBdQtjcgw2R2x/blBHWS3NCDDxvDlSeD9vW7BgDf3dgInz+OOKvuF1wzTZlFLcgtz9pmrNPCHAdkTnJNMwXo6Na1NsHxOTC9q3a1Y2q1WE5KK87CQ4HUM02pRb9u2reYzTKtFLZ3Mmh/DxNVCm2AOUjR5FQr70lOGCZW1CSYtai+NCAKvbTVjwAkRLBgRFoyIv4LJJ6SLj1rKaMZAWB+X/g8CyLBPEHcmu5NdGOwO7yPm5Qi2SzZuR+xeEpOqaALaLWoSqd8w0RRFRBVNQPu9ZGUuY58zhkUG0XW1F42qxgSCHcMKp5CMHMKIqjGBADLMTQTRpgn8ZtCoH+ygn7qNwXsxbDeoTwY8hvUhFr+ENlVjAtot6pq/+S6XKV7jRYAFI6Ldoq71Lqndoq55wTIv8CejeBJMjD3aBGOLmlkGC0aEBSPiu2CpgWaXRW3hkElWhcBXwaRYkcEuTBYnu3GIG0ujRPPxKpnCQHM3cPUuekPgTni+Spazmr3GikgrB11oN8leLQPfSxLhqyQR/zKssV10yEHcNuwPt0vxMcMa0d4F9JX84XYEhwybWvg6hjX23sVk1yAixXnYPiBewCWDPGq2qInwVZIIC0aELWoibFETYYuaCFvURPjWiAgLRoQFI+KvYMueomaLujJN51wW9SSipw27+VbLgGhE79VzmDhtzqPnwT5FLTHs0fMQ3Eua9ZxrCK6Sk0ga9Jxr8Blm2D83BJxhKQx09yF2ypx/bvA/w+71uSzqCJKn2KJWpXDDFrVPsGBE2KImwhY1EbaoibBFTSQEt0ZmwYIRYcGI+CtYGYvaNJva/wwrsagXw6R7Se6SRIK3qA0jYHtHhmGfW6GW/rFsDDPrUwXYrSDCgz6REIxhFpoN+tQ1tqiJ8BhGhAUjwhY1EbaoibBFTYQtaiI8cSXCghFhwYj4K1gZi9qk2yKJ/xm2xN65G4YPsSDAXZIIW9REArZ3zLKnJQGPYWbZ0xJ2K4jwoE+EM4wIW9REOMOIsGBE2KImwhY1EbaoibBFTYQnrkRYMCIsGJE60+yVoKkzzV4JGu6SRKxnM3PaphWzM9nan1a47wVXGy8C3CWJcIYR4QwjwhlGhAUjwoIRYcGIsEVNhC1qEsC/Qh5SBhDND7IAAAAASUVORK5CYII=

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    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

  • bknight 46549

    SSC Veteran

    Points: 270

    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),'|'), '|') 

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    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

  • sknox

    SSChampion

    Points: 12294

    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 .

  • Jeff Moden

    SSC Guru

    Points: 997150

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • HappyGeek

    SSCoach

    Points: 18684

    Jeff Moden - Sunday, December 17, 2017 8:48 PM

    Heh... I have a much easier solution.  Use the DelimitedSplit8K function for this and not worry about stuff breaking. πŸ˜‰

    +100

    ...

  • SQLWinther

    SSCertifiable

    Points: 5946

    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