7

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()?

Lamak
  • 2,576
  • 1
  • 24
  • 30
John K. N.
  • 17,649
  • 12
  • 51
  • 110

1 Answers1

6

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,
        PARSENAME(QUOTENAME(@Text2Quote_4,@QuoteChar_2),@ParseParam) AS Parsed_QuotedText_4

Output for square brackets [ ]

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)

and to complete things the code for the double quotes

Additional code for double quotes "

-- 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

Output for double quotes "

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)

Based on ypercube™'s feedback and Aaron Bertrand's new feedback we have come to a mutual conclusion

PARSENAME() is the inverse of QUOTENAME() if the following conditions are met:

PARSENAME(x,1)-1(QUOTENAME(x,q)) where q={1,2} and x='{'any_string_value'}

References:

robyaw
  • 103
  • 4
John K. N.
  • 17,649
  • 12
  • 51
  • 110
  • 1
    All of your examples use @QuoteChar_1 as the second argument to QUOTENAME(), why? This is not a valid character for a quoted identifier. SELECT 'column', [column], "column" FROM (SELECT [column] = 1) x; IMHO, the spirit of David's comment was that by default and without adding an uncommon character as the second argument, PARSENAME() acts like the inverse of QUOTENAME(). – Aaron Bertrand Jan 23 '18 at 14:39
  • Thanks for the feedback. In a similar spirit that is why my comment was that I didn't believe that PARSENAME was the reverse of QUOTENAME, and that I didn't think that the original question was about putting brackets around strings and removing them again. I understood the question to be "How does Microsoft parse Object Names..." (which is answered by PaulWhite) and in the end proving ypercube's thesis to be wrong. According to the documentation '(single quotes), " (double quotes) and [ ](square brackets) are valid quotation marks for the function QUOTENAME. I'll add more examples. – John K. N. Jan 23 '18 at 15:00
  • 1
    My point is not that single quotes aren't valid arguments to QUOTENAME(). It's that we don't use them in SQL Server to quote identifiers, and the question was clearly in the context of parsing identifiers. – Aaron Bertrand Jan 23 '18 at 15:21
  • Could we mutually agree that PARSENAME() is the opposite of QUOTENAME() IF QUOTENAME() is limited to " (double quotes) and [ ] (square brackets) AND the parameter object_piece for the PARSENAME() function is limited to 1? – John K. N. Jan 23 '18 at 15:29
  • Sure, I don't think anyone was trying to assert that they were inverse functions in all possible contrived scenarios. – Aaron Bertrand Jan 23 '18 at 15:29
  • 2
    I agree with the conclusion: PARSENAME(x , 1) is the inverse of QUOTENAME(x, q) when q is not the single quote ;) – ypercubeᵀᴹ Jan 23 '18 at 15:56
  • Fantastic. Thanks for the clarification. I'll add it to my answer. – John K. N. Jan 23 '18 at 15:58