So I have a column with the following values that some times has a number in the beginning
turtle boat
10 banana split
lord Thanos
23 macbook
How to query to remove numbers and the space in front of the numbers, but not the space in the middle of the string?
Intended output:
turtle boat
banana split
lord Thanos
macbook
Query I tried from another question in here:
select regexp_replace(mycolumn, '[^[:alpha:]]', '', 'g')
Problem with this query is that it completely removes all spaces
You can exclude all characters except of letters and spaces. Additionally, use trim() to remove leading or trailing spaces:
with my_data(mycolumn) as (
values
('turtle boat'),
('10 banana split'),
('lord Thanos'),
('23 macbook')
)
select trim(regexp_replace(mycolumn, '[^[:alpha:]\s]', '', 'g'))
from my_data
btrim
--------------
turtle boat
banana split
lord Thanos
macbook
(4 rows)
This will remove the leading digits and the space:
select regexp_replace('10 banana split', '^\d+ ', '');
/^\d+[ ]/
^ asserts position at start of a line
\d+ matches a digit (equal to [0-9])
+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)
[ ] matches the character ' ' literally (case sensitive)
The g modifier, for 'global', is why in your regex all spaces were removed, as matched as non-alpha characters.
fiddle here
