CODEWITHSUNDEEP
sql-servert-sql
Martin Brown
Martin Brown

Reputation: 25320

What is the opposite of the QUOTENAME function?

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?

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?

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

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

Upvotes: 14

Views: 5443

Answers (4)

Jorge Morales
Jorge Morales

Reputation: 1

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

Upvotes: 0

Stephan
Stephan

Reputation: 6018

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]

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294387

Use PARSENAME:

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


[foo]]]
foo]

Upvotes: 15

Sathish
Sathish

Reputation: 2066

First, create a SQL function:

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

Upvotes: 0

Related Questions