StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's a starting point. It's a function that breaks both emails down into (char-pos, char) pairs, then finds pairs that're mismatched between the two inputs.</p> <pre><code>CREATE OR REPLACE FUNCTION email_diffs( email1 IN text, email2 IN text, chnum OUT integer, ch OUT text, fromwhich OUT integer ) RETURNS SETOF RECORD AS $$ BEGIN RETURN QUERY WITH e1chars AS ( SELECT generate_series(0, length($1) -1 ) AS chpos, regexp_split_to_table($1,'') ch ), e2chars AS ( SELECT generate_series(0, length($2) - 1) AS chpos, regexp_split_to_table($2,'') ch ), only_in_e1chars AS ( SELECT * FROM e1chars EXCEPT SELECT * FROM e2chars ), only_in_e2chars AS ( SELECT * FROM e2chars EXCEPT SELECT * FROM e1chars ), mismatched_pairs AS ( SELECT *, 1 FROM only_in_e1chars UNION SELECT *, 2 FROM only_in_e2chars ) SELECT * FROM mismatched_pairs; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; </code></pre> <p>Results look like this:</p> <pre><code>regress=# SELECT * FROM email_diffs('fred@bo','fred@bob'); chnum | ch | fromwhich -------+----+----------- 7 | b | 2 (1 row) regress=# SELECT * FROM email_diffs('fred@bob','fred@bbo'); chnum | ch | fromwhich -------+----+----------- 6 | b | 2 6 | o | 1 7 | b | 1 7 | o | 2 (4 rows) </code></pre> <p>You should be able to call this with another CTE that tests for each of the errors you are interested in, or just extend the CTE in that main function with additional clauses to test for each case and return a verdict.</p> <p>Exactly how to do that will depend on the specifics of the rules you must test and how strict you have to be about detecting exactly one typo. You haven't specified that.</p> <hr> <p>You might have noticed the odd-looking use of a SELECT without a FROM clause in <code>e1chars</code> and <code>e2chars</code>, where two functions are called in the SELECT list. This is a very strange PostgreSQL extension to SQL that you really shouldn't generally use, as results are often not what you expect. PostgreSQL will support the SQL-standard <code>LATERAL</code> syntax in 9.3, and that should be used in preference.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload