CODEWITHSUNDEEP
regexdatabasepostgresql
Luis Henrique
Luis Henrique

Reputation: 771

How to extract the first two digits from a string?

I have the query below that returns me only the numeric values ​​of my string field, I wanted to limit it to two output characters.

QUERY

SELECT NULLIF(regexp_replace(temperature, '\D','','g'), '')::numeric AS "Temperature"
from tbl_temperature_hosts
where temperature like '%Core 0%' limit 1

OUTPUT

6601205698

EXPECTED OUTCOME

66

Upvotes: 0

Views: 558

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Use a single regular expression with back references:

NULLIF(regexp_replace(temperature, '^\D*(\d?)\D*(\d?).*$', '\1\2'), '')

^ and $ are optional here, but typically make it faster.

Or wrap your expression in left() - shorter & faster than substring():

left(NULLIF(regexp_replace(temperature, '\D','','g'), ''), 2)

db<>fiddle here

Upvotes: 2

Islingre
Islingre

Reputation: 2349

SELECT substring(NULLIF(regexp_replace(temperature, '\D','','g'), '')::numeric::text from 1 for 2)::numeric AS "Temperature"
from tbl_temperature_hosts
where temperature like '%Core 0%' limit 1

Upvotes: 2

Related Questions