double qoutes issue

Question

Hi Expert,

I want to remove the double qoutes from end and start of the statement

create table table1 ( col1 varchar(200))

create table  table1 ( col1 varchar(200))
insert into tale1 values('"Test1 Test1 Test1 "Test1" Test1"')
insert into tale1 values('"Test1 "Test1" Test1 Test1 Test1"')
insert into tale1 values('"Test1 Test1 Test1 Test1 "Test1"')
expected output:
Test1 "Test1" Test1 Test1 Test1
Test1 Test1 Test1 "Test1" Test1
Test1 Test1 Test1 Test1 "Test1" 

Answer 1

Check if this query is suitable in your case:

select trim('"' from col1) as result from table1

Answer 2

This would be trim output Test1 Test1 Test1 Test1 "Test1 in case we have double qoutes on last word then it did not work

Answer 3

Hi @Shambhu Rai

Have read all your comments, but still confused about your request.

For this row: "Test1 Test1 Test1 "Test1" Test1"

And you want this output: Test1 "Test1" Test1 Test1 Test1

You have post this:

1. remove the double qoutes from end and start of the statement.
2. the original data contains one word in double quotes but not fix wheather it is in last or in between. All three rows are different

You still need to explain why the second Test1 was double quoted in the output.

Best regards,

Cosmog Hong

Answer 4

Hi @Shambhu Rai, It seems that I finally got it what you need. Please try the following solution based on XML and XQuery. Overall, we are tokenizing string of tokens and via if/else logic making decision what to do with each token.

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(200));
INSERT @tbl (tokens) VALUES
('"Test1 Test1 Test1 "Test1" Test1"'),
('"Test1 "Test1" Test1 Test1 Test1"'),
('"Test1 Test1 Test1 Test1 "Test1"');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT * FROM @tbl;

SELECT t.* , c
	, c.query('
		for $x in /root/r/text()
		return if (substring($x,1,1)="""" and substring($x,string-length($x),1)="""") then string($x)
			else if (substring($x,1,1)="""" and substring($x,string-length($x),1)!="""") then string(substring($x,2, 200))
			else if (substring($x,1,1)!="""" and substring($x,string-length($x),1)="""") then string(substring($x,1, string-length($x)-1))
			else string($x)
	
	').value('.','VARCHAR(200)') AS result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c)

Answer 5

...

SELECT TRIM('"' FROM col1)
FROM table1