Postgres: extract integer prefix
Nov 11, 2021
To extract 123 (as a number) as a prefix from a string like “123 test test”, I can get the position of the first space, then getting the substring until that point, then cast it to string, so I get 123 casted as integer
SELECT CAST( SUBSTRING('123 test test' from 0 for POSITION(' ' IN '123 test test')) AS INTEGER)
-> 123
If I want the rest of the string “test test”, I can calculate the position as above, but adding +1 (to skip the space), then substring from this position. In case I want to trim potential double spaces, I can also trim leading spaces.
SELECT TRIM(LEADING ' ' from SUBSTRING('123 test test' from POSITION(' ' IN '123 test test')+1))
-> 'test test'