Postgres: extract integer prefix

Elvis Ciotti
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'

--

--

Elvis Ciotti
Elvis Ciotti

Written by Elvis Ciotti

Software Contractor — Java, Spring, k8s, AWS, Javascript @ London - hire me at https://elvisciotti.github.io/

No responses yet