QUOTENAME功能的反义词是什么？

In SQL Server there is a built in function that quotes an identifier for you called QUOTENAME. Is there a function that does the opposite and removes the quotes again?

在SQL Server中有一个内置函数，为您引用一个名为QUOTENAME的标识符。是否有相反的功能并再次删除引号？

In other words what do I replace SOMEFUNCTION with in the following code example to get it to return 1 for any value I could initialize @name to?

换句话说，在下面的代码示例中，我将如何替换SOMEFUNCTION以使其为任何可以初始化@name的值返回1？

declare @name nvarchar(50) = 'hello]'

select 
    case 
        when SOMEFUNCTION(QUOTENAME(@name)) = @name then 1
        else 0
    end

4 个解决方案

#1

Use PARSENAME:

使用PARSENAME：

declare @name sysname = quotename('foo]');
print @name;
set @name = parsename(@name, 1);
print @name;


[foo]]]
foo]

#2

First, create a SQL function:

首先，创建一个SQL函数：

CREATE FUNCTION dbo.[Revert_QUOTENAME](@quotedvalue As VARCHAR(8000)) 
RETURNS VARCHAR(8000)
As 
BEGIN
    RETURN REPLACE(SUBSTRING(@quotedvalue, 2, LEN(@quotedvalue) - 2), ']]', ']')
END

Then,

然后，

declare @name nvarchar(50) = 'hello]'

select 
    case 
        when [Revert_QUOTENAME](QUOTENAME(@name)) = @name then 1
        else 0
    end

#3

You can use case when logic to remove the quotes or brackets.

您可以在逻辑时使用大小写删除引号或括号。

DECLARE @yourTable TABLE (names VARCHAR(15));
INSERT INTO @yourTable VALUES('hello]'),('[hello]]'),('"hello]"');

SELECT  names AS QuotenameVal,
        CASE
            WHEN LEFT(names,1) = '"'
                 AND RIGHT(names,1) = '"'
                THEN SUBSTRING(names,2,LEN(names) - 2)
            WHEN LEFT(names,1) = '['
                 AND RIGHT(names,1) = ']'
                THEN SUBSTRING(names,2,LEN(names) - 2)
            ELSE names
        END Actual_Value
FROM @yourTable

Results:

结果：

QuotenameVal    Actual_Value
--------------- ---------------
hello]          hello]
[hello]]        hello]
"hello]"        hello]

#4

CODE;

码;

ALTER FUNCTION [dbo].[funObtenerSinQuoteName]
(@vchrCadena VARCHAR(8000) , @vchrQuoteNameIni CHAR(1),@vchrQuoteNameFin CHAR(1))  

RETURNS VARCHAR(8000) AS  
BEGIN 
    DECLARE @vchrSubCadena VARCHAR (8000), @intCharIndexIni INT,@intCharIndexFin INT

    SET @intCharIndexIni=CHARINDEX(@vchrQuoteNameIni,@vchrCadena,0)
    SET @intCharIndexFin=CHARINDEX(@vchrQuoteNameFin,@vchrCadena,0)
    SET @vchrSubCadena=LTRIM(RTRIM(SubString(@vchrCadena,@intCharIndexIni+1,@intCharIndexFin-@intCharIndexIni-1)))

    RETURN @vchrSubCadena
END

#1