CODEWITHSUNDEEP
postgresql
AdrianBR
AdrianBR

Reputation: 2588

Postgresql varchar to int

I have a clothes size column in postgresql containing values such as (24, 25, "S", "M", "ONESIZE", "XL").

I need to extract from it only the integer values and convert the result to int. I tried to_number in the format below, which works if the row contains a number, but does not work if it only contains letters.

select to_number('aq1', '99999D9') -> 1
select to_number('23', '99999D9') -> 23
select to_number('ONESIZE', '99999D9') -> error

I need a function that would work in a way that would allow me to join on the varchar column equal an int column.

select ???func("XL") -> null/0
select ???func(23) -> 23

Any help would be appreciated

Upvotes: 1

Views: 1361

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

I just found a way although it does not look very neat. Perhaps someone knows a native function?

select to_number(concat('ONESIZE',0), '99999D9')/10 -> 0
select to_number(concat('23',0), '99999D9')/10 -> 23

as per cathulhu's suggestion, concating with leading 0 to avoid the division

select to_number(concat(0,'23'), '99999D9') -> 23

Upvotes: 0

cathulhu
cathulhu

Reputation: 657

Try this one:

select COALESCE((select UNNEST(regexp_matches('XLds', '\d+')::integer[])), 0);

Notice that this regexp matches only first number in string.

Upvotes: 1

Related Questions