CODEWITHSUNDEEP
sqlsql-serverstored-procedures
spitfiredd
spitfiredd

Reputation: 3135

stored procedure to remove quotes

I am trying to write a stored procedure that will removed leading and trailing quotes from an arbitrary table and column. I keep getting an error saying that the table name isn't declared.

Here is the SP

create table [dbo].[test] (id nvarchar(20))
insert into dbo.test values ('"07617966004766"')
go

CREATE PROCEDURE sp_stripDoubleQuotes 
    @tableName sysname,
    @columnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE @tableName
    SET @columnName = SUBSTRING(@columnName, 2, LEN(@columnName))
    WHERE LEFT(@columnName, 1) = '"'

    UPDATE @tableName
    SET @columnName = SUBSTRING(@columnName, 1, LEN(@columnName)-1)
    WHERE RIGHT(@columnName, 1) = '"'

END
GO
exec [dbo].[sp_stripDoubleQuotes] N'[dbo].[test]', N'[id]'



select * from test

Here is a link to a fiddle: link to fiddle

Upvotes: 6

Views: 605

Answers (2)

LONG
LONG

Reputation: 4620

CREATE PROCEDURE sp_stripDoubleQuotes 
    @tableName sysname,
    @columnName sysname,
    @SQL varchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;    
    SET @SQL =

    'UPDATE ' + '[' + @tableName +']' +
    'SET' + '[' + @columnName +']' +'= SUBSTRING(' +'[' + @columnName +']' +', 2, LEN(' +'[' + @columnName +']' +'))
    WHERE LEFT('  + '[' + @columnName +']' +', 1) = '+'''"'''
    --PRINT(@SQL)
    EXEC (@SQL)

    SET @SQL =
    'UPDATE ' + '[' + @tableName +']' +
    'SET' + '[' + @columnName +']' +'= SUBSTRING(' + '[' + @columnName +']' +', 1, LEN(' + '[' + @columnName + ']' +')-1)
    WHERE RIGHT(' + '[' + @columnName +']' +', 1) = '+'''"'''
    --PRINT(@SQL)
    EXEC (@SQL)
END
GO
exec [dbo].[sp_stripDoubleQuotes] N'test', N'id'  -- exec [dbo].[sp_stripDoubleQuotes] N'[dbo].[test]', N'[id]'

Updated 2nd: I added [] to wrap table and column incase your table and column name have whitespace in them. Thanks @Sean Lange and @Richard

Updated 3rd: As @[benjamin moskovits] (xD) mentioned, if you hard coded brackets, the correct execute command is exec [dbo].[sp_stripDoubleQuotes] N'test', N'id'. Try to add or remove brackets and print to see whether the syntax is correct before executing it.

Upvotes: 6

MohanaPriyan
MohanaPriyan

Reputation: 218

CREATE PROCEDURE sp_stripDoubleQuotes 
    @tableName sysname,
    @columnName sysname
AS
BEGIN
    SET NOCOUNT ON;
    declare @QuerytoExecute varchar(1000) 
    set @QuerytoExecute="UPDATE "+@tableName+"
    SET "+@columnName+" = SUBSTRING("+@columnName+", 2, LEN("+@columnName+"))
    WHERE LEFT("+@columnName+", 1) = '""'";
    exec (@QuerytoExecute);
    set @QuerytoExecute="
    UPDATE "+@tableName+"
    SET "+@columnName+" = SUBSTRING("+@columnName+", 1, LEN("+@columnName+")-1)
    WHERE RIGHT("+@columnName+", 1) = '""'";
    exec (@QuerytoExecute);
END
GO

Upvotes: 0

Related Questions