STRING_SPLIT with no delimiter

Question

STRING_SPLIT with no delimiter

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

bknight 46549 SSC Veteran Points: 272 More actions · Answer 1

sknox - Monday, December 11, 2017 10:05 AM
bknight 46549 - Monday, December 11, 2017 7:29 AM
It seems to me the answer to this question is more about string manipulation through data type conversion than it is about String_Split function. And by altering the source string to fit the necessary parameters of the function, the original question has now been altered. By needing to have a deep understanding of the data type storage properties to get the correct answer, then this question is about that knowledge, not about understanding the String_Split function. The correct answer should have been "you cannot do this with String_Split() without first altering the string to include a separator".
I see where you're coming from -- you can't call STRING_SPLIT() directly on the original string to accomplish this, but that's only half of the point of this question. The other half is that there's often a way to accomplish a task which isn't directly supported, simply by changing the way you look at the data or the problem.
Yes, that does require some understanding of data types and how they are formatted. But when you're working with data at production scale, you should have that understanding, or the ability to research it quickly, anyway.
I always try to format my questions so that you can easily run the options in a test instance if you're not sure about the more technical aspects -- I don't consider these questions an exam on anyone's actual skill level, but an opportunity to share and learn.

I also see your point, and I do find many of these questions lead to learning experiences for me. I found this answer particularly intriguing as it isn't a solution I would have quickly thought of using. But I maintain the answer is out of place for the question. There are many different ways of adding a delimiter to a string so it can be passed to the function. And I suppose that the question of "How can I do this using String_Split() function" does beg for a solution requiring a manipulation of the input string. But the title of the question, and this discussion, is "STRING_SPLIT with no delimiter". A solution that adds a delimiter does not meet the requirement of the title of the question, and when one of the answers does, that should be the right answer. Maybe I am just splitting terminology hairs, but clarity of communication is often an issue for me.

Revenant SSC-Forever Points: 42467 More actions · Answer 2

Revenant

SSC-Forever

Points: 42467

December 11, 2017 at 11:34 am

#1971630

Very neat trick - thanks!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Nice question. Cool trick, well done. I learned something new today.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

One thing to note - Technically the correct answer would include
WHERE [value] <> ''
... otherwise the result set for

declare @a varchar(100) = 'ABCDEF';

SELECT value FROM STRING_SPLIT(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0))
... includes an empty row last which does not appear in the question 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Jeff Moden - Monday, December 11, 2017 10:29 AM
werner.grimmer - Monday, December 11, 2017 10:25 AM
I tried the answer on SQL Server 2016, the result is only one A?
THX
There's something wrong on your box or maybe just different... I tried it on 3 different 2016 boxes and it works just fine. What is your default language for the server?

same issue here...????

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

J Livingston SQL - Monday, December 11, 2017 1:02 PM
Jeff Moden - Monday, December 11, 2017 10:29 AM
werner.grimmer - Monday, December 11, 2017 10:25 AM
I tried the answer on SQL Server 2016, the result is only one A?
THX
There's something wrong on your box or maybe just different... I tried it on 3 different 2016 boxes and it works just fine. What is your default language for the server?
same issue here...????

That could be the difference. I'm running SP1 and you're not.

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Jeff Moden - Monday, December 11, 2017 1:33 PM
J Livingston SQL - Monday, December 11, 2017 1:02 PM
Jeff Moden - Monday, December 11, 2017 10:29 AM
werner.grimmer - Monday, December 11, 2017 10:25 AM
I tried the answer on SQL Server 2016, the result is only one A?
THX
There's something wrong on your box or maybe just different... I tried it on 3 different 2016 boxes and it works just fine. What is your default language for the server?
same issue here...????
That could be the difference. I'm running SP1 and you're not.
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

Nope ... ???

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sknox SSChampion Points: 12388 More actions · Answer 8

Carlo Romagnano - Monday, December 11, 2017 1:00 AM
Good to know!
I would add a where clause to avoid empty string
DECLARE @a VARCHAR(6) = 'ABCDEF' SELECT value FROM STRING_SPLIT(CAST(CAST(CAST(@A AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0)) WHERE value <>''

(+Alan Burstein)

Good point -- I missed that extra row in my original tests

sknox SSChampion Points: 12388 More actions · Answer 9

J Livingston SQL - Monday, December 11, 2017 2:56 PM
Jeff Moden - Monday, December 11, 2017 1:33 PM
J Livingston SQL - Monday, December 11, 2017 1:02 PM
Jeff Moden - Monday, December 11, 2017 10:29 AM
werner.grimmer - Monday, December 11, 2017 10:25 AM
I tried the answer on SQL Server 2016, the result is only one A?
THX
There's something wrong on your box or maybe just different... I tried it on 3 different 2016 boxes and it works just fine. What is your default language for the server?
same issue here...????
That could be the difference. I'm running SP1 and you're not.
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )
Nope ... ???

Odd. Can you try this:

declare @a varchar(6) = 'ABCDEF' select cast(cast(@A as nvarchar(12)) as varbinary(12)) select value from string_split(cast(cast(cast(@A as nvarchar(12)) as varbinary(12)) as varchar(12)), char(0))

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 10

It doesn't work on versions 13.0.4435 and 13.0.1601. So I would say that correct answer is "You cannot do this with STRING_SPLIT()."

My points are:
1. It is not reliable because it works on several versions of MS SQL Server which does have that bug (feature).
2. It is not elegant. Elegant solution would be like this:

DECLARE @a VARCHAR(6) = 'ABCDEF';

declare @nums as table (i tinyint);

insert into @nums(i) values (1), (2), (3), (4), (5), (6);

select substring(@a, i, 1) as [values] from @nums;

The @nums table is fixed size for the purpose of simplicity. But you can change this code in order to be able to work with string of any size using function LEN() and bigger table.
3. Also the solution in the question won't work with nvarchar strings, my code will work.

sknox SSChampion Points: 12388 More actions · Answer 11

Evgeny - Monday, December 11, 2017 4:11 PM
It doesn't work on versions 13.0.4435 and 13.0.1601. So I would say that correct answer is "You cannot do this with STRING_SPLIT()."
My points are:
1. It is not reliable because it works on several versions of MS SQL Server which does have that bug (feature).
2. It is not elegant. Elegant solution would be like this:
DECLARE @a VARCHAR(6) = 'ABCDEF';
declare @nums as table (i tinyint);
insert into @nums(i) values (1), (2), (3), (4), (5), (6);
select substring(@a, i, 1) as [values] from @nums;
The @nums table is fixed size for the purpose of simplicity. But you can change this code in order to be able to work with string of any size using function LEN() and bigger table.
3. Also the solution in the question won't work with nvarchar strings, my code will work.

The inconsistency is interesting. Based on the evidence so far, I'm not so sure it's down to specific versions; it's worked on the versions I've tested on, including SQL Server 2017 (14.0.1000.169).

The question wasn't about whether or not this was a good, elegant, or the best way to accomplish this; it was simply about whether it's possible. I would not recommend using this without extensive testing, both for compatibility and performance reasons.

I'd like to continue testing this on different versions and with different settings and see if we can identify what variables lead to this not working. Could you run the test code in my post directly above yours and share the results? Thanks!

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 12

sknox - Monday, December 11, 2017 6:38 PM
I'd like to continue testing this on different versions and with different settings and see if we can identify what variables lead to this not working. Could you run the test code in my post directly above yours and share the results? Thanks!

Looks like majority of people got the same result:
0x410042004300440045004600 A

The collation on my servers is Latin1_General_CI_AS, versions are 13.0.4435 and 13.0.1601. I think that code might work on a server with a binary collation, but right now I don't have one handy.

sknox SSChampion Points: 12388 More actions · Answer 13

Evgeny - Monday, December 11, 2017 7:06 PM
sknox - Monday, December 11, 2017 6:38 PM
I'd like to continue testing this on different versions and with different settings and see if we can identify what variables lead to this not working. Could you run the test code in my post directly above yours and share the results? Thanks!
Looks like majority of people got the same result:
0x410042004300440045004600 A
The collation on my servers is Latin1_General_CI_AS, versions are 13.0.4435 and 13.0.1601. I think that code might work on a server with a binary collation, but right now I don't have one handy.

No, my servers are SQL_Latin1_General_CP1_CI_AS as well. If I were to guess, I'd say it's a string component interpreting CHAR(0) as a string terminator rather than testing the length of the string. What confuses me is that behavior should not change that much between versions, which is why I'm looking for a configuration difference.
The varbinary results show that the full 12 characters are still there. I wonder what you'd get with:
declare @a varchar(6) = 'ABCDEF' --select cast(cast(@A as nvarchar(12)) as varbinary(12)) select cast(cast(cast(cast(@A as nvarchar(12)) as varbinary(12)) as varchar(12)) as varbinary(12))
That might show if the cast to varchar is truncating the results or not.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

sknox - Monday, December 11, 2017 4:07 PM
J Livingston SQL - Monday, December 11, 2017 2:56 PM
Jeff Moden - Monday, December 11, 2017 1:33 PM
J Livingston SQL - Monday, December 11, 2017 1:02 PM
Jeff Moden - Monday, December 11, 2017 10:29 AM
werner.grimmer - Monday, December 11, 2017 10:25 AM
I tried the answer on SQL Server 2016, the result is only one A?
THX
There's something wrong on your box or maybe just different... I tried it on 3 different 2016 boxes and it works just fine. What is your default language for the server?
same issue here...????
That could be the difference. I'm running SP1 and you're not.
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )
Nope ... ???
Odd. Can you try this:
declare @a varchar(6) = 'ABCDEF' select cast(cast(@A as nvarchar(12)) as varbinary(12)) select value from string_split(cast(cast(cast(@A as nvarchar(12)) as varbinary(12)) as varchar(12)), char(0))

with results to text......

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ed Wagner SSC Guru Points: 287026 More actions · Answer 15

That's a great question with a very inventive and really cool solution. I don't think I would have thought of it. Thanks!

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions