none
Remove Leading & Trailing quotes RRS feed

  • Question

  • Hi Experts,

    I have searched the internet but didn't found a simplified approach of removing leading and trailing double quotes from string. Am using SQL Server 2016 , If we have any new function available will be best or please support with alternative approach. I have a column from which the quotes need to be removed and the length of the string data varies.

    "Jake.1234@abc.com"
    Output - Jake.1234@abc.com
    
    "HelloWorld@gmail.com"
    
    Output - HellowWorld@Gmail.com
    
    "SQLServer2016@devessentials.com"
    Output - SQLServer2016@devessentials.com

    Thanks

    Priya

    Thursday, April 26, 2018 3:33 AM

Answers

  • One way

    Declare @Sample Table(MyData varchar(50)); Insert @Sample(MyData) Values ('"Jake.1234@abc.com"'), ('"HelloWorld@gmail.com"'), ('"SQLServer2016@devessentials.com"'), ('ValueWithoutQuotes');

    ;With cte As
    (Select MyData,
      Case When MyData In ('', '"', '""') Then ''
        Else Stuff(MyData, 1, 1, Case When Left(MyData, 1) = '"' Then '' Else Left(MyData, 1) End)
      End As LeadingQuoteRemoved
    From @Sample)
    Update cte
    Set MyData =
      Case When LeadingQuoteRemoved = '' Then ''
        Else Stuff(LeadingQuoteRemoved, Len(LeadingQuoteRemoved), 1, Case When Right(LeadingQuoteRemoved, 1) = '"' Then '' Else Right(LeadingQuoteRemoved, 1) End)
      End;

    Select * From @Sample;

    Tom

    • Marked as answer by Priya Bange Thursday, April 26, 2018 3:26 PM
    Thursday, April 26, 2018 4:51 AM
  • if its SQL 2017 you can simply do this

    SELECT TRIM('"' FROM Column) AS Column FROM TableName

    for earlier versions, do something like

    SELECT CASE WHEN CHARINDEX('"',Column) = 1 OR CHARINDEX('"',REVERSE(Column)) =1 THEN STUFF(STUFF(Column,PATINDEX('%[^"]"',Column)+1,LEN(Column),''),1,PATINDEX('%[^"]%',Column)-1,'') ELSE Column END FROM TableName


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, April 26, 2018 5:39 AM
    • Marked as answer by Priya Bange Thursday, April 26, 2018 3:26 PM
    Thursday, April 26, 2018 5:34 AM

All replies

  • One way

    Declare @Sample Table(MyData varchar(50)); Insert @Sample(MyData) Values ('"Jake.1234@abc.com"'), ('"HelloWorld@gmail.com"'), ('"SQLServer2016@devessentials.com"'), ('ValueWithoutQuotes');

    ;With cte As
    (Select MyData,
      Case When MyData In ('', '"', '""') Then ''
        Else Stuff(MyData, 1, 1, Case When Left(MyData, 1) = '"' Then '' Else Left(MyData, 1) End)
      End As LeadingQuoteRemoved
    From @Sample)
    Update cte
    Set MyData =
      Case When LeadingQuoteRemoved = '' Then ''
        Else Stuff(LeadingQuoteRemoved, Len(LeadingQuoteRemoved), 1, Case When Right(LeadingQuoteRemoved, 1) = '"' Then '' Else Right(LeadingQuoteRemoved, 1) End)
      End;

    Select * From @Sample;

    Tom

    • Marked as answer by Priya Bange Thursday, April 26, 2018 3:26 PM
    Thursday, April 26, 2018 4:51 AM
  • if its SQL 2017 you can simply do this

    SELECT TRIM('"' FROM Column) AS Column FROM TableName

    for earlier versions, do something like

    SELECT CASE WHEN CHARINDEX('"',Column) = 1 OR CHARINDEX('"',REVERSE(Column)) =1 THEN STUFF(STUFF(Column,PATINDEX('%[^"]"',Column)+1,LEN(Column),''),1,PATINDEX('%[^"]%',Column)-1,'') ELSE Column END FROM TableName


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, April 26, 2018 5:39 AM
    • Marked as answer by Priya Bange Thursday, April 26, 2018 3:26 PM
    Thursday, April 26, 2018 5:34 AM
  • Hi Priya,

    Please try below Query

    CREATE Table #tmp_table(column_name varchar(50));
    Insert #tmp_table(column_name) Values
    ('"Jake.1234@abc.com"'),
    ('"HelloWorld@gmail.com"'),
    ('"SQL"Server2016@devessentials.com"'),
    ('ValueWithoutQuotes'),
    ('ValueWithoutQuotes');

    select replace(replace(replace('$$$'+column_name+'$$$','$$$"',''),'"$$$',''),'$$$','')
    from #tmp_table

    drop table #tmp_table

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue


    vishnu dalwadi

    Thursday, April 26, 2018 6:53 AM
  • Try this too..
    Declare @temp Table(val varchar(50));
    Insert @temp Values
    ('"Jake.1234@abc.com"'),
    ('"HelloWorld@gmail.com"'),
    ('"SQLServer2016@devessentials.com"');
    
    select replace(val,'"','') from @temp
    Thursday, April 26, 2018 12:59 PM
  • Declare @temp Table(val varchar(50));
    Insert @temp Values
    ('"Jake.1234@abc.com"'),
    ('"HelloWorld@gmail.com"'),
    ('"SQLServer2016@devessentials.com"');
    select SUBSTRING(val,CHARINDEX('"',val)+1,len(val)-2) from @temp;
    
    


    Cheers, Saravanan

    Thursday, April 26, 2018 3:39 PM