Postgresql interval to Integer-CodePudding

Currently I have a column call days and the values are 1 day, 2 days etc. I am trying to extract or keep only the numeric value.

Something like:

select CAST(LEFT(days, CHARINDEX(' ', days)) as integer) as #days from daily_table

CodePudding user response：

You could first cast the string to an interval, then use extract():

select extract(day from days::interval) as "#days"
from daily_table;

CodePudding user response：

You can use substring() function to get the numeric value and then cast to INTEGER

select cast(substring(days, 1,  position('d' in days)-2) as INTEGER)
as "#days" from daily_table;