patternsqlMinor

Is the function PARSENAME() the opposite of QUOTENAME()

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thefunctionparsenameoppositequotename

Problem

In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author McNets had noticed, that some internal functions could handle quoted (e.g. [MyTable]) and unquoted (e.g. MyTable) parameter values passed on (e.g. OBJECT_ID()).

In the answer (1) given by David Browne - Microsoft the quote was made that:

...the inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.

I disagreed with this part of the answer with the following comment:

I do agree that you can modify an argument passed to PARSENAME() to display the parts of a multi-part identifier be it the SERVER, DATABASE, SCHEMA or OBJECT part. But IMHO stating that PARSENAME is the opposite of QUOTENAME is a bit far-fetched.

My question now is:

Is the function PARSENAME() the opposite of QUOTENAME()?

Solution

Based on the comment by ypercubeᵀᴹ which reads:

...can you find a string x that parsename(quotename(x),1) will be different than x? If not, that falls into the definition of "inverse function" ;) ...

... I created a simple statement to have a look at the different values returned by these functions. The code is as follows:

Code

DECLARE 
-- Text that I will be converting using QUOTENAME()
@Text2Quote_1 AS NVARCHAR(20),
@Text2Quote_2 AS NVARCHAR(20),
@Text2Quote_3 AS NVARCHAR(20),
@Text2Quote_4 AS NVARCHAR(20),
-- The characters used for "quotation"
@QuoteChar_1 AS NCHAR(1),
@QuoteChar_2 AS NCHAR(1),
@QuoteChar_3 AS NCHAR(1),
-- The Parsing option as defined in the original MS documnenation for PARSSENAME()
@ParseParam AS INT

SET @Text2Quote_1 = N'Test'
SET @Text2Quote_2 = N'[Test]'
SET @Text2Quote_3 = N'Test.dbo.test'
SET @Text2Quote_4 = N'[Test].[dbo].[test]'

SET @QuoteChar_1 = ''''
SET @QuoteChar_2 = '[' 
SET @QuoteChar_3 = '"' 

SET @ParseParam = 1 -- Parsing level : Object name
-- checking the results for ' single quotes
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_1) AS QuotedText_1, 
        QUOTENAME(@Text2Quote_2,@QuoteChar_1) AS QuotedText_2, 
        QUOTENAME(@Text2Quote_3,@QuoteChar_1) AS QuotedText_3, 
        QUOTENAME(@Text2Quote_4,@QuoteChar_1) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_1, 
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_4

Basically, I set a string values, output that value, QUOTENAME() and output the value, then PARSENAME() the QUOTENAME()ed value and output it.

Results

The results were quite interesting:

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4          
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]     

(1 row(s) affected)                                                                                

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4            
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   '[Test]'                 'Test.dbo.test'          '[Test].[dbo].[test]'   

(1 row(s) affected)                                                                                

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4     
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   NULL                     test'                    NULL                    

(1 row(s) affected)

When the base text Test.dbo.test (OriginalText_3) is passed on to the QUOTENAME() function it gets converted to: 'Test.dbo.test' (QuotedText_3)

When the QuotedText_3 string 'Test.dbo.test' is passed on to the PARSENAME() function it is converted to: test' (Parsed_QuotedText_3)

Conclusion

Seeing as I have proven ypercubeᵀᴹ's thesis wrong, I think it is safe to state that the function PARSENAME() is not the inverse of QUOTENAME().

Based on Aaron Bertrand's feedback here the code for square brackets [ ] (add to original script)

Additional Code for square brackets [ ]

```
-- checking the results for [ brackets
SELECT @Text2Quote_1 AS OriginalText_1,
@Text2Quote_2 AS OriginalText_2,
@Text2Quote_3 AS OriginalText_3,
@Text2Quote_4 AS OriginalText_4
SELECT QUOTENAME(@Text2Quote_1,@QuoteChar_2) AS QuotedText_1,
QUOTENAME(@Text2Quote_2,@QuoteChar_2) AS QuotedText_2,
QUOTENAME(@Text2Quote_3,@QuoteChar_2) AS QuotedText_3,
QUOTENAME(@Text2Quote_4,@QuoteChar_2) AS QuotedText_4
select PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_1,
PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_2,
PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_3

Code Snippets

DECLARE 
-- Text that I will be converting using QUOTENAME()
@Text2Quote_1 AS NVARCHAR(20),
@Text2Quote_2 AS NVARCHAR(20),
@Text2Quote_3 AS NVARCHAR(20),
@Text2Quote_4 AS NVARCHAR(20),
-- The characters used for "quotation"
@QuoteChar_1 AS NCHAR(1),
@QuoteChar_2 AS NCHAR(1),
@QuoteChar_3 AS NCHAR(1),
-- The Parsing option as defined in the original MS documnenation for PARSSENAME()
@ParseParam AS INT

SET @Text2Quote_1 = N'Test'
SET @Text2Quote_2 = N'[Test]'
SET @Text2Quote_3 = N'Test.dbo.test'
SET @Text2Quote_4 = N'[Test].[dbo].[test]'

SET @QuoteChar_1 = ''''
SET @QuoteChar_2 = '[' 
SET @QuoteChar_3 = '"' 

SET @ParseParam = 1 -- Parsing level : Object name
-- checking the results for ' single quotes
SELECT  @Text2Quote_1 AS OriginalText_1,
        @Text2Quote_2 AS OriginalText_2,
        @Text2Quote_3 AS OriginalText_3,
        @Text2Quote_4 AS OriginalText_4
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_1) AS QuotedText_1, 
        QUOTENAME(@Text2Quote_2,@QuoteChar_1) AS QuotedText_2, 
        QUOTENAME(@Text2Quote_3,@QuoteChar_1) AS QuotedText_3, 
        QUOTENAME(@Text2Quote_4,@QuoteChar_1) AS QuotedText_4
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_1, 
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_1),@ParseParam) AS Parsed_QuotedText_4

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4          
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]     

(1 row(s) affected)                                                                                

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4            
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   '[Test]'                 'Test.dbo.test'          '[Test].[dbo].[test]'   

(1 row(s) affected)                                                                                

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4     
------------------------ ------------------------ ------------------------ ------------------------
'Test'                   NULL                     test'                    NULL                    

(1 row(s) affected)

-- checking the results for [ brackets                                                                           
SELECT  @Text2Quote_1 AS OriginalText_1,                                                                         
        @Text2Quote_2 AS OriginalText_2,                                                                         
        @Text2Quote_3 AS OriginalText_3,                                                                         
        @Text2Quote_4 AS OriginalText_4                                                                          
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_2) AS QuotedText_1,                             
        QUOTENAME(@Text2Quote_2,@QuoteChar_2) AS QuotedText_2,                             
        QUOTENAME(@Text2Quote_3,@QuoteChar_2) AS QuotedText_3,                             
        QUOTENAME(@Text2Quote_4,@QuoteChar_2) AS QuotedText_4                              
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_4

OriginalText_1           OriginalText_2           OriginalText_3           OriginalText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

QuotedText_1             QuotedText_2             QuotedText_3             QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
[Test]                   [[Test]]]                [Test.dbo.test]          [[Test]].[dbo]].[test]]]

(1 row(s) affected)

Parsed_QuotedText_1      Parsed_QuotedText_2      Parsed_QuotedText_3      Parsed_QuotedText_4
------------------------ ------------------------ ------------------------ ------------------------
Test                     [Test]                   Test.dbo.test            [Test].[dbo].[test]

(1 row(s) affected)

-- checking the results for [ brackets                                                                           
SELECT  @Text2Quote_1 AS OriginalText_1,                                                                         
        @Text2Quote_2 AS OriginalText_2,                                                                         
        @Text2Quote_3 AS OriginalText_3,                                                                         
        @Text2Quote_4 AS OriginalText_4                                                                          
SELECT  QUOTENAME(@Text2Quote_1,@QuoteChar_3) AS QuotedText_1,                             
        QUOTENAME(@Text2Quote_2,@QuoteChar_3) AS QuotedText_2,                             
        QUOTENAME(@Text2Quote_3,@QuoteChar_3) AS QuotedText_3,                             
        QUOTENAME(@Text2Quote_4,@QuoteChar_3) AS QuotedText_4                              
select  PARSENAME(QUOTENAME(@Text2Quote_1,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_1,
        PARSENAME(QUOTENAME(@Text2Quote_2,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_2,
        PARSENAME(QUOTENAME(@Text2Quote_3,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_3,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_3),@ParseParam) AS Parsed_QuotedText_4

Context

StackExchange Database Administrators Q#196015, answer score: 6

Revisions (0)

No revisions yet.