It's a pretty common frustration when you're working in SQL Server and suddenly run into the invalid object name 'string_split' error right when you're trying to get things done. You might be following a tutorial or trying out a new script, only to have the database engine act like it has no idea what you're talking about. It's annoying because STRING_SPLIT is supposed to be one of those handy, built-in functions that makes life easier, yet there it is, throwing a wrench in your workflow.
The core of the problem usually boils down to one of two things: either your SQL Server version is a bit behind the times, or your database is stuck pretending it's an older version. It's a classic case of the software being capable of doing something, but the settings are holding it back. Let's dig into why this happens and how you can actually get your code running without pulling your hair out.
Why SQL Server thinks it doesn't exist
The STRING_SPLIT function was introduced in SQL Server 2016. If you're running a version older than that—like 2014, 2012, or heaven forbid, 2008 R2—you're going to see that "invalid object name" message every single time. The engine literally doesn't have the code for it. However, the more confusing scenario is when you know you're on SQL Server 2016, 2017, 2019, or 2022, and it still fails.
This usually happens because of the compatibility level of the specific database you're using. SQL Server has this feature where a single database can "act" like an older version of SQL Server to ensure that old legacy code doesn't break when you migrate to a new server. Even if your physical server is the latest and greatest version, if your database compatibility level is set to something lower than 130 (which represents SQL Server 2016), it won't recognize STRING_SPLIT. It's like trying to run a modern app on a phone that thinks it's still running an OS from 2010.
Checking your compatibility level
Before you go changing a bunch of settings, you'll want to see where you actually stand. You can check the compatibility level of your database with a quick query. Just run something like this:
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
If the number that pops up is 120 or lower, that's your culprit. To use STRING_SPLIT, that number needs to be at least 130. It's a weirdly specific detail that catches a lot of people off guard because they assume that a server upgrade automatically updates everything underneath it. It doesn't—and for good reason, though it's definitely a pain in this specific instance.
How to fix the compatibility issue
If you've confirmed that the compatibility level is the issue, fixing it is straightforward, provided you have the right permissions. You can bump the level up using an ALTER DATABASE command.
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 130;
(Or you can set it higher if you're on a newer version, like 140 for 2017 or 150 for 2019).
Once you run that, try your query again. In most cases, the invalid object name 'string_split' error will vanish instantly. One word of caution: changing the compatibility level can sometimes change how the query optimizer behaves. If you have a massive, complex production database with very specific performance tuning, you might want to test this in a dev environment first just to make sure no other old queries start acting funky. For most people, though, it's a perfectly safe and necessary move.
What if you can't change the settings?
Sometimes you're working in an environment where you don't have the authority to change database settings. Maybe the DBA is strict, or maybe the database is part of a legacy third-party app that specifically requires an older compatibility level to stay supported. If that's the case, you're stuck in a bit of a bind. You can't use the built-in function, so you have to go back to the "old ways."
Before 2016, we all used custom User-Defined Functions (UDFs) to split strings. It wasn't as clean as a built-in function, but it got the job done. You can find plenty of "SplitString" functions online that use WHILE loops or XML tricks to break down a comma-separated string into a table. While it's not as fast as the native STRING_SPLIT, it's a reliable workaround if the built-in version is off the table.
The "Old School" XML trick
If you don't want to create a permanent function in the database, there's a somewhat messy but effective trick using XML. It looks a bit intimidating if you haven't seen it before, but it essentially turns your string into an XML tag and then uses SQL's XML processing to parse it.
It looks something like this: ```sql DECLARE @string VARCHAR(MAX) = 'apple,banana,cherry' DECLARE @xml XML = CAST('
SELECT f.x.value('.', 'VARCHAR(MAX)') AS Part FROM @xml.nodes('/root/x') AS f(x) `` Is it pretty? No. Is it efficient for millions of rows? Definitely not. But does it work whenSTRING_SPLIT` is giving you that "invalid object name" error? Absolutely. It's a handy tool to keep in your back pocket for those locked-down environments.
Common syntax gotchas
Even if the function is recognized, sometimes people think it's broken because they aren't using it quite right. Remember that STRING_SPLIT returns a table, not a single value. You can't just use it in a SET statement or a simple SELECT. You usually have to use it in the FROM clause or with a CROSS APPLY.
If you try to use it like a scalar function (one that returns a single piece of data), SQL Server might throw a different error, but if you're mismanaging the schema or the database context, you could still end up looking at an object name error. Always make sure you're actually connected to the database you think you are. It sounds silly, but I can't tell you how many times I've been frustrated by an error only to realize I was accidentally running my script against the master database instead of my actual project database.
A note on the return column
Another thing to keep in mind once you do get it working is the column name. STRING_SPLIT returns a table with a single column, and for versions before SQL Server 2022, that column is always named value. If you try to reference it by any other name without aliasing it, you'll get a "column not found" error, which might lead you back down the rabbit hole of thinking the function itself is the problem.
In SQL Server 2022, they finally added an optional third parameter to return the index of the split items, which is super helpful. But if you're just trying to get past the invalid object name 'string_split' hurdle, you're likely on an older version anyway, so sticking to the basic value column is the way to go.
Wrapping things up
Dealing with the invalid object name 'string_split' error usually feels like a bigger deal than it actually is. Most of the time, it's just a quick settings tweak or a realization that the server version is a little older than you thought. Whether you fix it by bumping up the compatibility level or by using a custom workaround, the goal is to get your data moving again.
SQL Server can be picky about these things, but once you understand the relationship between the version and the compatibility settings, these kinds of errors become much easier to spot and fix. Just check your version, verify that compatibility level, and you'll be back to splitting strings in no time.