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)) SQLCopy

create table  table1 ( col1 varchar(200))
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

Answer 1

@Shambhu Rai - Thanks for the question and using MS Q&A platform.

To remove the double quotes from the start and end of the string values in your SQL query, you can use the regexp_replace function in Databricks.

Here's an example of how you can modify your SQL query to achieve the expected output:

create table table1 (col1 string);

insert into table1 values('"Test1 Test1 Test1 "Test1" Test1"');
insert into table1 values('"Test1 "Test1" Test1 Test1 Test1"');

select regexp_replace(col1, '^"|"$', '') as col1 from table1;

In this modified query, the regexp_replace function is used to remove the double quotes from the start and end of the col1 column. The ^"|"$ regular expression pattern matches the double quotes at the start and end of the string, and the regexp_replace function replaces them with an empty string.

The output of this query will be:

Test1 "Test1" Test1 Test1 Test1
Test1 Test1 Test1 "Test1" Test1

Here is the executed notebook screenshot:

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.