0

I have a database table with email addresses with apostrophe such as "some.o'ne@somewhere.com".

I would like to:

  1. Query this table, and check if this email address exist
  2. insert this email address if it doesn't exist in the table

I tried:

SELECT * 
FROM EMAILTABLE 
WHERE emailaddress LIKE 'some.o''ne@somewhere.com'

it doesn't find "some.o'ne@somewhere.com", so it's treating it as it doesn't exist!

Ruby
  • 1
  • 1
  • Well, `some.o''ne@somewhere.com` is different than `"some.o'ne@somewhere.com"`. If your column contains double quotes, the obviously you need to include them in the comparison value. Also: the LIKE doesn't really make sense here, as you are not using any wildcards, so it' s the same as `=` –  Jan 21 '22 at 19:47
  • I just tested out your example in a postgres database using dbeaver-ce as client. It worked fine. If you have trouble, try to escape your single quotes using \' instead of ' – John Nielsen Jan 21 '22 at 20:00
  • [Works for me.](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=7537fe09b5830918c8aedcdfe874ae10) As others already commented, are the double quotes part of the data or did yous use them in your text to delimit the value as verbatim? – sticky bit Jan 21 '22 at 20:20
  • The database contains single quote, and I can't change the database. when doubling the single quote to query the DB it's not finding the email address with single quote! I also tried using \' instead but it's failing! – Ruby Jan 24 '22 at 08:54

2 Answers2

0

Double-quotes around some text indicates you are looking for an object name, unless you are embedding the double-quotes within single-quotes.

So:

DROP TABLE IF EXISTS emailtable;

CREATE TEMP TABLE emailtable (emailaddress text);

CREATE UNIQUE INDEX idx_emailtable_emailaddress ON emailtable (emailaddress);

INSERT INTO emailtable (emailaddress) VALUES ('some.o''ne@somewhere.com'); 

SELECT emailaddress
,(emailaddress = 'some.o''ne@somewhere.com')::bool as escaped_apostrophe_single_quotes --true because it matches the value in the table
--,(emailaddress = "some.o'ne@somewhere.com")::bool as double_quotes --ERROR: column "some.o'ne@somewhere.com" does not exist
,(emailaddress = '"some.o''ne@somewhere.com"')::bool as double_quotes --false because the value in the table doesn't include double-quotes
FROM emailtable;

In the second test, it's assuming that "some.o'ne@somewhere.com" is a column, because it's a name inside double-quotes.

You can see from the first and third tests that the presence of the double-quotes inside the string are saying that these strings are different. The first one is the same as the value in the table. The other one isn't.

Anyway, I think the simplest way to handle is with a unique index on the emailaddress. If you then try to insert the same value that already exists, it won't do anything.

INSERT INTO emailtable (emailaddress) VALUES ('some.o''ne@somewhere.com') ON CONFLICT DO NOTHING;

enter image description here

FlexYourData
  • 2,081
  • 1
  • 12
  • 14
0

Thanks for the replies (just replacing single quote with two single quotes doesn't work for me as it stores the email addresses with two single quotes) This query worked fine for me:

SELECT * FROM EMAILTABLE WHERE emailaddress = (E'some.o\'ne@somewhere.com')::text;
Ruby
  • 1
  • 1