0

String Split in SQL

SQL has a inbuilt function to split string values based on specific character. String_Split() - fn. In this if we pass two arguments as '1,2,3' and ',' It will split them and give result as 1 2 3 - which we can store in table and use. I am using this fn to split the variable and get max value from it. It is working fine, but when I give any one of the inputs in the range of 10 to 19 among the variable input max value is not coming correctly. Expected Result: To find maximum value from string after splitting it Code Compiled: Select max(value) from string_split('1,2,10', ',') Output: 2 Note: Try compiling: Select max(value) from string_split('1,2,20', ',') Output: 20 It is working correctly

4th Feb 2020, 7:07 PM
Ram Prakash R
Ram Prakash R - avatar
5 Answers
+ 1
Hi Ram Prakash R , Please tell me the result you get for maximum of splitted of string '10 ... 19', is it 10, 19 or other? And I'm not too sure about this, but considering the data we're working on here is a string, I suppose the comparison would be done by ASCII value. I'm guessing this because the string contains only numbers (separated by commas), in which case there's just little or no possibility for a factor of character encoding issue.
5th Feb 2020, 2:51 AM
Ipang
+ 1
Hi Ipang, I just modified the post, including the Expected outcome. I am not getting the correct output only if I use the vales 10 to 19 in string as maximum values, using the above mentioned query. Please let me know if you know the cause of the issue.
5th Feb 2020, 2:41 AM
Ram Prakash R
Ram Prakash R - avatar
+ 1
Hi Ipang, Found the issue, it is just as you said. Because of ASCII conversion, it is getting failed. I tried converting the data type and now it is working fine. Thanks for the help.
5th Feb 2020, 3:55 AM
Ram Prakash R
Ram Prakash R - avatar
+ 1
You're welcome buddy 👌
5th Feb 2020, 4:43 AM
Ipang
0
So the expected result was 20 in the second query right? I'm missing the issue here ... ?
5th Feb 2020, 2:25 AM
Ipang