SQL Server - PARSENAME Function

Similar to the QUOTENAME function, another one of those often unknown or overlooked functions is the PARSENAME function. I’ll confess that I’m not entirely sure why it isn’t PARSE_NAME, inline with other functions such as OBJECT_NAME etc. but then I guess the string functions all seem to be concatenated. Anyhoo, let’s ignore all of that for now.

The beauty of this function is that it allows you to get the individual components of a given 4-part object name presented as a string. So, given '[ServerNameFoo].[DatabaseBar].[SchemaBaz].[ObjectCaz]' we can get the server name component or the database component if we want, maybe to ensure that what we are evaluating exists in the current database for example. We can also get the schema or object names too. I agree that its usefulness is probably limited to esoteric edge cases but it is one of those functions that when you need it, it’s really handy. Especially when you consider trying to implement this functionality yourself and catering for object names with or without square brackets or full stops etc. in their name.

To use the function you would write something like:

SELECT PARSENAME('server.database.schema.object', 2);

This would return the value “schema”. Note that the function doesn’t evaluate whether or not the object exists and doesn’t care. All it does is return the requested component of the string if it is available else NULL. For an example of it in use, have a look at the internals of the sp_spaceused stored procedure. (i.e. Execute sp_helptext 'sp_spaceused'.)

Also note that there are some articles on the web about using this function in order to split strings for other reasons (eg. IP Addresses) which could be quite a neat solution. (See: http://www.sqlteam.com/article/using-the-parsename-function-to-split-delimited-data) However, remember that the function is SQL Server delimiter aware so you need to be careful. Running the function on the string 'foo.[bar]'  to return the object name component will return "bar" and not "[bar]".

I’ll be honest and say that I’ve never really had a reason to use it but at least I know it’s there if I do. And now you do too. :-)