How can I remove leading and trailing quotes in SQL Server

Question

I have a table in a SQL Server database with an NTEXT column   This column may contain data that is enclosed with double quotes  When I query for this column  I want to remove these leading and trailing quotes     For example    this is a test message   should become  this is a test message  I know of the LTRIM and RTRIM functions but these workl only for spaces   Any suggestions on which functions I can use to achieve this

User · Answer

The following script removes quotation marks only from around the column value if table is called  Messages  and the column is called  Description    -- If the content is in the form of  anything   LIKE        -- Then take the whole text without the first and last characters  --  from the 2nd character and the LEN  Description   - 2th character   UPDATE  Messages  SET  Description    SUBSTRING  Description   2  LEN  Description   - 2  WHERE  Description  LIKE

User · Answer

You can use following query which worked for me-   For updating-  UPDATE table SET colName  REPLACE LTRIM RTRIM REPLACE colName                       WHERE      For selecting-  SELECT REPLACE LTRIM RTRIM REPLACE colName                       FROM TableName

User · Answer

you could replace the quotes with an empty string     SELECT AllRemoved   REPLACE CAST MyColumn AS varchar max                     LeadingAndTrailingRemoved   CASE             WHEN MyTest like       THEN SUBSTRING Mytest  2  LEN CAST MyTest AS nvarchar max   -2             ELSE MyTest            END   FROM   MyTable

User · Answer

I use this   UPDATE DataImport SET PRIO            CASE WHEN LEN PRIO   lt  2          THEN               CASE PRIO WHEN      THEN    ELSE PRIO END           ELSE REPLACE PRIO        SUBSTRING PRIO  2  LEN PRIO  - 2                      SUBSTRING PRIO  2  LEN PRIO  - 2            END

User · Answer

Try this   SELECT left right cast SampleText as nVarchar  LEN cast sampleText as nVarchar  -1  LEN cast sampleText as nVarchar  -2    FROM TableName

User · Answer

You can use TRIM     FROM   this  is  a test    which returns  this  is  a test

User · Answer

You can simply use the  Replace  function in SQL Server   like this     select REPLACE  this is a test message            note  second parameter here is  double quotes  inside two single quotes and third parameter is simply a combination of two single quotes  The idea here is to replace the double quotes with a blank   Very simple and easy to execute

User · Answer

I thought this is a simpler script if you want to remove all quotes  UPDATE Table Name SET col name   REPLACE col name

User · Answer

Some UDFs for re-usability   Left Trimming by character  any number   CREATE FUNCTION  dbo   LTRIMCHAR    Input NVARCHAR max    TrimChar CHAR 1         RETURNS NVARCHAR max  AS BEGIN       RETURN REPLACE REPLACE LTRIM REPLACE REPLACE  Input             TrimChar               TrimChar            END   Right Trimming by character  any number   CREATE FUNCTION  dbo   RTRIMCHAR    Input NVARCHAR max    TrimChar CHAR 1         RETURNS NVARCHAR max  AS BEGIN      RETURN REPLACE REPLACE RTRIM REPLACE REPLACE  Input             TrimChar               TrimChar            END   Note the dummy character       Alt 0166  cannot be present in the data  you may wish to test your input string  first  if unsure or use a different character

User · Answer

To remove both quotes you could do this   SUBSTRING fieldName  2  lEN fieldName  - 2    you can either assign or project the resulting value

User · Answer

CREATE FUNCTION dbo TRIM  String VARCHAR MAX    Char varchar 5   RETURNS VARCHAR MAX  BEGIN RETURN SUBSTRING  String PATINDEX          Char          String        DATALENGTH  String  2 -  PATINDEX          Char              REVERSE  String     PATINDEX          Char          String          END GO  Select dbo TRIM   this is a test message          Reference   http   raresql com 2013 05 20 sql-server-trim-how-to-remove-leading-and-trailing-charactersspaces-from-string

User · Answer

I have just tested this code in MS SQL 2008 and validated it   Remove left-most quote   UPDATE MyTable SET FieldName   SUBSTRING FieldName  2  LEN FieldName   WHERE LEFT FieldName  1          Remove right-most quote   Revised to avoid error from implicit type conversion to int   UPDATE MyTable SET FieldName   SUBSTRING FieldName  1  LEN FieldName -1  WHERE RIGHT FieldName  1

User · Answer

I know this is an older question post  but my daughter came to me with the question  and referenced this page as having possible answers  Given that she s hunting an answer for this  it s a safe assumption others might still be as well   All are great approaches  and as with everything there s about as many way to skin a cat as there are cats to skin   If you re looking for a left trim and a right trim of a character or string  and your trailing  character string is uniform in length  here s my suggestion   SELECT SUBSTRING ColName VAR  LEN ColName -VAR    Or in this question     SELECT SUBSTRING   this is a test message   2  LEN   this is a test message   -2    With this  you simply adjust the SUBSTRING starting point  2   and LEN position  -2  to whatever value you need to remove from your string    It s non-iterative and doesn t require explicit case testing and above all it s inline all of which make for a cleaner execution plan

User · Answer

My solution is to use the difference in the the column values length compared the same column length but with the double quotes replaced with spaces and trimmed in order to calculate the start and length values as parameters in a SUBSTRING function    The advantage of doing it this way is that you can remove any leading or trailing character even if it occurs multiple times whilst leaving any characters that are contained within the text   Here is my answer with some test data    SELECT    x AS before    SUBSTRING x          LEN x  -  LEN LTRIM REPLACE x                    - 1    1 --start pos         LEN LTRIM REPLACE x              --length          AS after FROM    SELECT  test      AS x UNION ALL SELECT            AS x UNION ALL SELECT   test     AS x UNION ALL SELECT  test      AS x UNION ALL SELECT   test     AS x UNION ALL SELECT    test    AS x UNION ALL SELECT  test      AS x UNION ALL SELECT    test    AS x UNION ALL SELECT   te st    AS x UNION ALL SELECT  te st     AS x   a   Which produces the following results   before  after ----------------- test    test       test   test test    test  test   test   test  test test    test   test      test  te st  te st te st   te st   One thing to note that when getting the length I only need to use LTRIM and not LTRIM and RTRIM combined  this is because the LEN function does not count trailing spaces

[sql] How can I remove leading and trailing quotes in SQL Server?

Examples related to sql

Examples related to sql-server