1

I need to select column from a table removing " ' and space and that is to be exported in a csv file. for example if the column contain "155/229-230 Moo 10 , Nuanchan Road" I shall have to remove the leading and ending quotes to 155/229-230 Moo 10 , Nuanchan Road

I was trying

SELECT '"SQL Server String Function"'
    , substring('"SQL Server String Function"', patindex('%[^"'' ]%','"SQL Server String Function"'), LEN('"SQL Server String Function"')) position;

but it is not removing the ending quotes.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Debanjan Hazra
  • 11
  • 1
  • 2
    Why do the spaces in your example remain? – sticky bit Jul 06 '20 at 21:56
  • Can you just do a `replace()` for the quote characters? – shawnt00 Jul 06 '20 at 22:07
  • 2
    I'm not understanding why you need to remove spaces, quotes and apostrophes. Correct CSV encoding can handle spaces, commas, quotes and even linebreaks in field data as per [RFC 4180 Common Format and MIME Type for Comma-Separated Values (CSV) Files](https://tools.ietf.org/html/rfc4180). – AlwaysLearning Jul 06 '20 at 23:52
  • Your sample above contains a comma. If you remove the quotes, this value will actually be two columns when output to a csv. – Trubs Jul 07 '20 at 04:59

1 Answers1

0

In SQL 2017 TRIM was introduced.

Your code would look like this:

SELECT TRIM( '"'' ' FROM  '"SQL Server String Function"') AS Result

Note: I don't have access to SQL 2017 to give you a working sample.

For earlier versions it is a little more complicated (credit goes to Randi Vertongen):

DECLARE @pat VARCHAR( 10 )= '%[^''" ]%'
DECLARE @strTbl TABLE( string VARCHAR( 40 ))
INSERT INTO @strTbl VALUES
( '''" SQL Server "String Function "''' ),
( '''" SQL Server "String Function' ),
( 'SQL Server "String Function' ),
( 'SQL Server "String Functio''n' );

SELECT RIGHT( string, LEN( string ) - PATINDEX( @pat, string ) + 1 ) -- Remove leading
FROM
    ( SELECT LEFT( string, LEN( string ) - PATINDEX( @pat, REVERSE( string )) + 1 ) AS string -- Remove trailing
    FROM @strTbl ) AS a;

How it works:

  • '%[^''" ]%' - is looking for the first non-excluded character in string
  • PATINDEX( @pat, REVERSE( string )) - finds the last non-excluded character

Limitations

Due to LEN function not counting trailing spaces, this logic will work correctly if you want to keep trailing spaces.

Alex
  • 4,885
  • 3
  • 19
  • 39