主页 / dba / 问题 / 196015
Accepted
John K. N.
Asked: 2018-01-24 06:18:27 +0800 CST

函数 PARSENAME() 是否与 QUOTENAME() 相反

  • 772

在问题中 MS-SQL 上是否有任何（隐藏的）内置函数来取消引用对象名称？作者正在寻求有关是否存在“取消引用”引用字符串的内部函数的建议。作者McNets注意到，一些内部函数可以处理带引号的（例如[MyTable]）和不带引号MyTable的（例如 ）参数值（例如OBJECT_ID()）。

在David Browne - Microsoft给出的答案 ( 1 )中，引述如下：

... QUOTENAME 的倒数是 PARSENAME，它具有导航多部分名称的额外能力。

我不同意这部分答案，并发表以下评论：

我同意您可以修改传递给的参数PARSENAME()以显示多部分标识符的部分，无论是、SERVER还是部分。但恕我直言，说这与 的相反有点牵强。DATABASESCHEMAOBJECTPARSENAMEQUOTENAME

我现在的问题是：

函数PARSENAME()是相反的QUOTENAME()吗？

sql-server functions

1 个回答

  1. Best Answer

    根据ypercubeᵀᴹ的评论，内容如下：

    ...你能找到一个不同于的字符串x吗？如果不是，则属于“反函数”的定义；）...parsename(quotename(x),1)x

    ...我创建了一个简单的语句来查看这些函数返回的不同值。代码如下：

    代码

    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

    基本上，我设置一个字符串值，输出该值，QUOTENAME()然后输出该值，然后PARSENAME()是QUOTENAME()ed 值并输出它。

    结果

    结果非常有趣：

    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)

    当基本文本Test.dbo.test( OriginalText_3 ) 被传递给QUOTENAME()它被转换为的函数时：'Test.dbo.test'( QuotedText_3 )

    当QuotedText_3字符串'Test.dbo.test'传递给PARSENAME()函数时，它被转换为：test'( Parsed_QuotedText_3 )

    结论

    鉴于我已经证明ypercubeᵀᴹ的论文是错误的，我认为可以肯定地说该函数PARSENAME()不是QUOTENAME().

    根据Aaron Bertrand的反馈，方括号的代码[ ]（添加到原始脚本）

    方括号的附加代码[ ]

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

    双引号的输出"

    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)

    根据ypercube™的反馈和Aaron Bertrand的新反馈，我们得出了共同的结论

    PARSENAME()是QUOTENAME()如果满足以下条件的倒数：

    PARSENAME(x,1)-1(QUOTENAME(x,q))在哪里q={1,2}和x='{'any_string_value'}

    参考：

    • 6

相关问题