Normally when I write a SQL Tip I do so because I want to show how to do something cool or use a particular function/feature of SQL Server. I’m writing this one because I stumbled upon some nuances or funky behavior that I’d like to write about to help me remember it. 🙂 Don’t worry, I’ll still write about a cool way to use it – just for you.
An object, such as a View, belongs to a Schema which belongs to a Database which belongs to a Server. So, the view “v_R_System” can also be referred to as “CM_XYZ.dbo.v_R_System”; in this case we are “3 part naming” the view with the database (“CM_XYZ”), schema (“dbo”), and object (“v_R_System”) names. As a side note, it’s best practice to use two part naming in your queries which is why you’ll notice my examples written with the “schema.object” format.
PARSENAME is meant to retrieve the different parts of a SQL object. So, in our example above, PARSENAME can retrieve the Server, Database, Schema, or Object portion of a given object name. However, if the object name passed to the function only contains the Object name you aren’t going to get anything for the Schema portion – it can only return the parts that are passed in.
You access the different parts of the object using “part numbers”. These are basically index locations, or maybe “positional” or “ordinal” numbers is a better way to say it. The indexes or positional locations are numbered right to left – 1 through 4. If all four parts are used, the Object is number one and the Server is number four. Note: any number not between 1 and 4 used in the function will return a NULL rather than an error.
Let’s look at the example I used above in SQL (the output is below the code):
SELECT PARSENAME(N'CM_XYZ.dbo.v_R_System',1) AS [Object Part] ,PARSENAME(N'CM_XYZ.dbo.v_R_System',2) AS [Schema Part] ,PARSENAME(N'CM_XYZ.dbo.v_R_System',3) AS [Database Part] ,PARSENAME(N'CM_XYZ.dbo.v_R_System',4) AS [Server Part];
As you may have guessed, it’s essentially parsing out the portion of the string between the periods (since that is how an object is fully qualified). If the part doesn’t exist then it returns NULL (see the “Server Part” from the screenshot above).
Honestly, I don’t think I’ve ever used this function to get actual object names. I use it to parse other strings though. Perhaps for a concatenated string that I pass around and then want to parse or a version column for example. The version column is the one that I remember using more often in the past few years than any other example so let’s talk about that.
Versions are strings (at least in the tables/columns I’ve been using); generally because a version can have multiple periods in it. If you’ve ever tried to ORDER BY a version column like this you’ll know that the order is not what you’d expect. Take the following example:
SELECT Version0 FROM (VALUES (N'10.1'),(N'1.9'),(N'2.1'),(N'3.0.12'),(N'1.9.5')) tst(Version0) ORDER BY Version0;
You’d expect “10.1” to be the last record since “10” is larger than “1”, “2”, and “3” but it was ordered as part of the “1s”. That’s because it’s a string and not a number. The reason I bring this up is because it easily illustrates how SQL will order or compare these strings: if you start comparing these versions using things like ‘less than’ or ‘greater than’ you’re not going to get results you’d expect. If you can guarantee that the version will only have one period you can convert the version to float or decimal and things should work much better. But, as you know many versions have more parts to them than just a “major” and a “minor” part.
Which brings me to the point of needing to pull out the different parts of the version for comparisons. This is where PARSENAME comes in really handy; use it to do the parsing and then you can convert the outcome to a number and do your comparisons. For example:
DECLARE @Win10RTMVersion nvarchar(50) = N'10.0.10240.0'; SELECT PARSENAME(@Win10RTMVersion,4) ,PARSENAME(@Win10RTMVersion,3) ,PARSENAME(@Win10RTMVersion,2) ,PARSENAME(@Win10RTMVersion,1);
I could end this post right there and call it good…but instead, let me anticipate a possible problem you may run into. You may have versions with various numbers of periods; such as iOS “12.1” along with Win10 “10.0.10240”. The problem you can run into with this and PARSENAME is that the “Major” version will be accessible with different “part numbers” because the parts are positional and they start from the right rather than the left. Let’s look at what I mean:
SELECT Version0 ,PARSENAME(Version0,4) AS [ Part 4 ] ,PARSENAME(Version0,3) AS [ Part 3 ] ,PARSENAME(Version0,2) AS [ Part 2 ] ,PARSENAME(Version0,1) AS [ Part 1 ] FROM (VALUES (N'10.0.10240.0'),(N'10.0.10240'),(N'12.1')) tst(Version0);
See that? Each of those versions show the major version in different columns, or in other words, we had to access the major version part (using different numbers – 2, 3, and 4). In order to handle this issue you can do some case/isnull/etc statements to access the major version with the same part number or do something like this:
SELECT Version0 ,PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))+1) ,PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))) ,PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))-1) ,PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))-2) FROM (VALUES (N'10.0.10240.0'),(N'10.0.10240'),(N'12.1')) tst(Version0);
Pretty nice right? This way we get all the version parts in left to right order. How we did that was by determining the number of periods in the version column. You can do that by getting the length of the column and subtract the length of the column after removing all the periods. Then we simply add, subtract, or do nothing to that number of periods found and we get the right part of the version (left to right). To illustrate this in SQL (I added another version – one with just one number to help with the illustration):
SELECT Version0 ,LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')) AS [NumOfPeriods] ,CONVERT(nvarchar(20),LEN(Version0)-LEN(REPLACE(Version0,N'.',N''))+1) + N' = ' + ISNULL(CONVERT(nvarchar(10),PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))+1)),N'NULL') AS [Left Most Part Number] ,CONVERT(nvarchar(20),LEN(Version0)-LEN(REPLACE(Version0,N'.',N''))) + N' = ' + ISNULL(CONVERT(nvarchar(10),PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N''))))),N'NULL') AS [2nd Part Number (From Left)] ,CONVERT(nvarchar(20),LEN(Version0)-LEN(REPLACE(Version0,N'.',N''))-1) + N' = ' + ISNULL(CONVERT(nvarchar(10),PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))-1)),N'NULL') AS [3rd Part Number (From Left)] ,CONVERT(nvarchar(20),LEN(Version0)-LEN(REPLACE(Version0,N'.',N''))-2) + N' = ' + ISNULL(CONVERT(nvarchar(10),PARSENAME(Version0,(LEN(Version0)-LEN(REPLACE(Version0,N'.',N'')))-2)),N'NULL') AS [4th Part Number (From Left)] FROM (VALUES (N'10.0.10240.0'),(N'10.0.10240'),(N'12.1'),(N'5')) tst(Version0);
Hopefully that explains it well enough. It makes sense in my head…I just hope I was able to explain it well.
To recap a few of the limitations or rules of using this function:
- If more than 3 periods exist the function doesn’t work – it returns NULL regardless of what part number you try to get
- If a number other than 1, 2, 3, or 4 is used as the part number it returns NULL
- The part numbers are “read” right to left starting with “1”
- If a valid part number is used but not enough “parts” exist in the value it returns NULL
I realize this is already a pretty long post, but remember how I wanted to write about some nuances so I don’t forget? Well, here they are…for me. You’re welcome to continue reading if you wish.
The problem I ran into was trying to parse a value I intended to perform a LIKE against. This works just fine in most cases but if I tried using special wildcard features it would not work. Having percent signs and underscores works fine. But what if I wanted to escape the underscore to look specifically for an underscore rather than any character? In a normal LIKE statement I could do that by surrounding the underscore with brackets – that tells SQL to look for an underscore rather than “any single character”; like so:
WHERE ViewName LIKE 'v[_]R[_]%'
(That would only match on the views that start with “v_R_” and therefore NOT something like “vwRe”).
If however I used PARSENAME to get that same value from a variable to use in a LIKE statement it would fail. That’s because PARSENAME returns NULL for that value. In investigating this, I found that the characters ‘[‘, ‘]’, and ‘”‘ (ASCII codes 91, 93, and 34) can cause PARSENAME to return NULL.
It sorta makes sense though if you think about it. Double quotes and brackets are used to reference/create identifiers. Have you ever had SQL Object Explorer script an object for you? If so, you probably noticed that instead of having “FROM dbo.v_R_System” it had “FROM [dbo].[v_R_System]”.
If an object name has brackets or double quotes around it PARSENAME removes them during the parsing. Using our previous example:
SELECT PARSENAME('[dbo].[v_R_System]',2) AS [Schema] ,PARSENAME('[dbo].[v_R_System]',1) AS [Object];
But, if one of those characters is in the middle of the string? It will return NULL:
SELECT PARSENAME(N'Some Value',1) AS [No Wildcards] ,PARSENAME(N'%Some Value%',1) AS [Percent Wildcards] ,PARSENAME(N'Some_Value_',1) AS [Underscore Wildcards] ,PARSENAME(N'%So%Val%',1) AS [Multiple Percent Wildcards] ,PARSENAME(N'Some[_]Value',1) AS [Escaping an Underscore Wildcard] ,PARSENAME(N'"Some" Value',1) AS [Having double quotes in the string (not wildcards)];
I believe that is because during the parsing not only does it look at the periods to determine the part, it also looks for quotes and brackets – specifically at the start and end of the part – and strips them out.
So, is there a way around this? Yes. You have to escape them with double quotes; I didn’t get it to work with brackets. Here’s an example of escaping:
SELECT SomeValue ,PARSENAME(SomeValue,4) AS [First Word] ,PARSENAME(SomeValue,3) AS [Second Word] ,PARSENAME(SomeValue,2) AS [Third Word] ,PARSENAME(SomeValue,1) AS [Fourth Word] FROM (VALUES (N'[This]."Will".Not.Work') ,(N'[This]."Will".Not.Work"') ,(N'[This]."Will".Totally."Work"') ,(N'[This]."Will".Totally."Work"""') ,(N'[This]."Will".Also."""Work [_] """') ) tst(SomeValue);
Hopefully some part of this was useful to you and you’ll be able to put PARSENAME to use for you. And, hopefully I won’t have to learn this lesson again about how to use PARSENAME with special characters when I’m trying to do something tricky.