Postgresql trimming string up to the first digit

Question

I have a table for postal codes, like N9Z3W. I want to copy only the 'N' part to a different column. Basically I need to cut off everything after the first digit

NE9ZW becomes NE

L9C3K3 becomes L

How can I do this using postgres (in a single update query)

Answer 1

CREATE TABLE meuk
        (oldval text
        , newval text
        );

insert into meuk(oldval) values
 ('NE9ZW' )
, ('L9C3K3' )
        ;

UPDATE meuk
SET newval = regexp_replace (oldval, '([A-Z]*).*', '\1' )
        ;
SELECT * FROM meuk;

---

Result:

---

 oldval | newval 
--------+--------
 NE9ZW  | NE
 L9C3K3 | L
(2 rows)

BTW: this does not strictly consumes onto the first digit, but all the leading oppercase characters. For upto something, use a negated (with ^) pattern, like this:

UPDATE meuk
SET newval = regexp_replace (oldval, '([^0-9]*)[0-9].*', '\1' )
        ;