Remove trailing letters from a string in BigQuery SQL

Question

I have ID values in a table that look like these: ABC12345 AAZ54321 ZDA67890

Some of them, however, have trailing letters that I would like to get rid of, like these: ABC12345A AAZ54321AZ

Is there a way to use SQL to remove any trailing letters from these strings?

Answer 1

We can use the REGEXP_REPLACE() function with the pattern [A-Za-z]+$:

UPDATE yourTable
SET ID = REGEXP_REPLACE(ID, r'[A-Za-z]+$', '')
WHERE REGEXP_CONTAINS(ID, r'[A-Za-z]$');

Note that if you just want to view the ID with trailing letters stripped, we can use a select query:

SELECT REGEXP_REPLACE(ID, r'[A-Za-z]+$', '') AS ID
FROM yourTable;