String Split in SQL

Question

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

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

You're welcome buddy 👌

Answer 5

So the expected result was 20 in the second query right? I'm missing the issue here ... ?