Why STRING_SPLIT is invalid while string_split is fine?

Question

IntelliSense keeps complaining that STRING_SPLIT is invalid object name,
while string_split, STRING_AGG, string_agg are all acceptable,
and related queries work perfectly well.

Completed these but issue persists...
1. Refresh Local Cache
2. Reinstall SSMS 18.6
3. Repair SQL Server 2019 Standard
4. Install latest update SQLServer2019-KB4563110-x64

Answer 1

Could it be to do with collation? Case sensitive?

Works fine on my SQL Server 2019 Developer edition using same SSMS version.

Answer 2

Works fine for me too (SQL Server 2019 Developer). What is your SQL Server version (select @@VERSION)? 

I believe there was a problem with the earlier versions of SSMS (SSMS 18.4 ?) but it got fixed at some point.

Also, can you double check (just in case) that you don't have UDF with the same name.

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 3

It is apparently a bug in Intellisense.  It is not recognizing string_split as an internal function.  If you look closely at the drop down you will see string_split is lowercase.

You can submit a bug here https://feedback.azure.com/forums/908035-sql-server

Answer 4

Works fine for me in upper case locally.

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 5

The problem went away when I updated to SSMS 18.6.  I cannot explain why the OP is still having problems, since the screen print shows 18.6.

Answer 6

The problem went away when I updated to SSMS 18.6.  I cannot explain why the OP is still having problems, since the screen print shows 18.6.

As SG_87 suggested, this is collation-related. When I try

SELECT * FROM STRING_SPLIT('1,2,2,3', ',')
SELECT * FROM string_split('1,2,2,3', ',')

I get red squiggles in tempdb, where I have Finnish_Swedish_CS_AI as the collation. When I move to a database with Danish_Norwegian_CI_AS the squiggles go away. I also get the squiggles in a database with Turkish_CI_AS. (Because in Turkish, upper('i') <> 'I')

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Collation indeed. Mine's SQL_Latin1_General_CP1_CS_AS. Didn't relate the issue to collation because the peer STRING_AGG was never squiggled.

Erland Sommarskog's trial prompted me to look into that. So I simply collated to SQL_Latin1_General_CP1_CI_AS and the squiggles vanished. Reverting to SQL_Latin1_General_CP1_CS_AS brings them back.

With such understanding, STRING_SPLIT or string_split doesn't matter as long as statements execute. Was just too curious. Suppose it's a bug then.

Thanks all.

Answer 8

It sounds like 18.6 fixed some but not all collations.  Thanks Erland