将varchar转换为int [英] Postgresql varchar to int

问题描述

我在postgresql中有一个衣服尺寸列，其中包含（24，25， S， M， ONESIZE， XL）之类的值。

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

我只需要从中提取整数值并将结果转换为int即可。
我尝试使用以下格式的to_number，如果行包含数字，则可以使用该格式，但是如果仅包含字母，则不能使用。

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

我需要一个函数，该函数的工作方式应允许我加入varchar列

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

任何帮助将不胜感激

推荐答案

尽管看起来并不整洁，但我刚刚找到了一种方法。也许有人知道本机函数？

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

根据cathulhu的建议，并以0开头，以避免除法

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

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

这篇关于将varchar转换为int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！