Search code examples
sqlstringgoogle-bigquery

Remove trailing letters from a string in BigQuery SQL

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?

Solution

  • 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;