Using regex to return distinct records

Question

I have a database table with multiple transaction records whose 'payee' column contains these kind of entries:

"DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 551"<br>
"DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 552"<br>
"DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 554"<br>
"DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 14"<br>
"DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 15"<br>
"DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 16"<br>
"DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 723"<br>
"DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 724"<br>
"DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 725"<br>

I would like to query the table in such a way as to get this list of "distinct" 'payee's:

"AXA INSURANCE UK"<br>
"VODAFONE LIMITED"<br>
"GOOGLE IRELAND LTD"<br>

My logic assumes I can execute a "distinct" type query incorporating a regex on the 'payee' column but I'm struggling.

Thank you nbk. Here is the pertinent line of code:
$query1 = mysql_query ("SELECT DISTINCT TRIM(REPLACE(SUBSTRING_INDEX(payee,'REF',1),'DIRECT DEBIT PAYMENT TO','')) FROM transactions", $Link); ...that gives empty results.

When I use this query, I get the dataset I published above: $query1 = mysql_query ("SELECT DISTINCT payee FROM transactions WHERE date <= '$today' AND date >= '$backDate' and (payee like 'direct debit%' || payee LIKE '%standing order%') ORDER BY payee", $Link);

nbk · Accepted Answer · 2020-04-03 16:24:38Z

1

You can use SUBSTRING_INDEX to eliminate the REF and number , and REPLACE the equal beginning

CREATE TABLE table1 (
  `atext` VARCHAR(52)
);

INSERT INTO table1
  (`atext`)
VALUES
  ('DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 551'),
  ('DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 552'),
  ('DIRECT DEBIT PAYMENT TO AXA INSURANCE UK REF 554'),
  ('DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 14'),
  ('DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 15'),
  ('DIRECT DEBIT PAYMENT TO VODAFONE LIMITED REF 16'),
  ('DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 723'),
  ('DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 724'),
  ('DIRECT DEBIT PAYMENT TO GOOGLE IRELAND LTD REF 725');

✓

✓

SELECT DISTINCT TRIM(REPLACE(SUBSTRING_INDEX(atext,'REF',1),'DIRECT DEBIT PAYMENT TO','')) FROM table1;

| TRIM(REPLACE(SUBSTRING_INDEX(atext,'REF',1),'DIRECT DEBIT PAYMENT TO','')) |
| :------------------------------------------------------------------------- |
| AXA INSURANCE UK                                                           |
| VODAFONE LIMITED                                                           |
| GOOGLE IRELAND LTD                                                         |

db<>fiddle here

answered Apr 3, 2020 at 16:24

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lquinlan Over a year ago

This is great. Thank you! I get the logic and have tested tweaks on fiddle. When I add this query to my code though (php mysql_query () ) it does not give me the same results. Just lots of empty rows. I changed the db 'payee' column from text to varchar but still no joy. What am I missing?

nbk Over a year ago

please enable error handling and add php code to your question

lquinlan Over a year ago

ok. when I try the query on phmypadmin I get the results im looking for, so something is up with my php page. My bad...ill investigate that. But also everything else that's not in the format im testing for here (direct debit - ref etc) is being returned. I should just be able to add another condition to my query to exclude them right ? Or does the existing distinct/trim/replace query need to be tweaked?

Collectives™ on Stack Overflow

Using regex to return distinct records

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related