CODEWITHSUNDEEP
sqldouble-quotes
user3447653
user3447653

Reputation: 4158

Parsing a string with double quotation in it

I have a table in which one of the fields contains the string 302720"?.

When I try to do a substring of that string, it returns an error. I understand that it is because of the double quotes within the string.

I tried replacing the quotation with REGEXP_REPLACE, even that didn't work.

Below is the SQL statement:

SELECT SUBSTR("302720"?", 0, 3)

Any comments regarding this would be appreciated.

Upvotes: 0

Views: 428

Answers (2)

Schwern
Schwern

Reputation: 165198

If you're doing this by hand, either use different quotes...

SELECT SUBSTR('302720"?', 0, 3)

Or you can escape the quote. You can do this by doubling the quote:

SELECT SUBSTR("302720""?", 0, 3)

Or you can use the traditional \ escape character.

SELECT SUBSTR("302720\"?", 0, 3)

If you're doing this in a program, use a prepared statement with bind parameters. This avoids having to deal with escapes as well of avoiding a host of security problems. The specifics differ by language, but it's usually something like this:

handle = connection.prepare("SELECT SUBSTR(?, ?, ?)");
handle.execute('302720"?', 0, 3)
result = handle.fetch

It's analogous to passing variables into a function.

Upvotes: 1

user4974662
user4974662

Reputation:

Please see below for an example. You didn't list the DBMS, so I assumed SQL Server.

Code

CREATE TABLE SUBSTR_TEST(STRING VARCHAR(50) NOT NULL)
INSERT INTO SUBSTR_TEST
VALUES('302720"?')

SELECT * FROM SUBSTR_TEST

SELECT SUBSTRING(REPLACE(STRING,'"',''),0,3) AS STRING FROM SUBSTR_TEST

Result

enter image description here

Upvotes: 1

Related Questions