Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




PARSENAME() - a simple way to parse (some) strings in SQL

Handy reminder: The PARSENAME() function can be useful for parsing small strings.  It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.

For example:

PARSENAME('most.valuable.yak',3) returns “most”

PARSENAME('most.valuable.yak',1) returns “yak”

PARSENAME('most.valuable.yak',4) returns NULL

This can be handy when you need to parse an IP address, or very simple CSV strings.  Just REPLACE() the commas with periods and you are good to go.  Just remember you are working backwards.

This is a system function, designed to parse SQL Server 4-part names, so it only works with periods and strings with 4 parts.  But it can be useful!

Print | posted on Tuesday, September 30, 2003 5:09 PM | Filed Under [ Miscellaneous T-SQL Code Library - SQL ]




I've already remembered this function... due to you.
10/16/2003 7:32 AM | Stoad


This was very helpful I did not know it was limited to 4 parts, you might want to state if you try to work with a string that has more than 4 parts you will recieve nulls for an answer
5/31/2006 9:56 AM | glenn
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET