Regex to remove " ' and space from column

Question

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.

Answer 1

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.