将Varchar转换为Float / double [英] Convert Varchar to Float/double
问题描述
我有'Age'列(Varchar)
如何选择查询并将其转换为(float)
I have column 'Age' (Varchar) How can I select query and convert it to (float)
宠物名 > Age(varchar)
John ; 2年6个月。
Anne 3年6个月。
Pet_Name Age(varchar)
John 2 years 6 months.
Anne 3 years and 6 months.
输出:
宠物名   ; 年龄(浮动/双)
约翰 2.5
Anne &bb ; 3.5
output:
Pet_Name Age(float/double)
John 2.5
Anne 3.5
我的问题是输入内容没有遵循特定的日期/年龄格式,而是以字符串形式输入。
my problem is that the inputs does not follow a specific date/age format and was entered as string.
推荐答案
假设age列始终有两个数字,我使用了 REGEXP_SUBSTR 函数在Redshift中编写以下答案:
Assuming the age column always has two numbers, I have used REGEXP_SUBSTR function in Redshift to write below answer:
create temp table pets (petname varchar(10), age varchar(20));
insert into pets values ('john','2 years 6 mnths');
insert into pets values ('anne','3 Years and 4 months');
insert into pets values ('buddy','4 yrs and 3 Mnths');
insert into pets values ('tommy','9 Years and 5 mnths');
insert into pets values ('alex','5 YEARS and 12 mnts');
insert into pets values ('bob','0 year and 7 Mnts');
insert into pets values ('danny','10 years 11 mnths');
insert into pets values ('sunny','81 years 10 mnths');
select petname,(REGEXP_SUBSTR(AGE,'[0-9]|[0-9][0-9]',1))::integer+(REGEXP_SUBSTR(AGE,'[0-9]|[0-9][0-9]',3))/12 as age from pets;
+--------------------+
|petname | age |
+--------------------+
|john |2.5000 |
|sunny |81.8333 |
|danny |10.9166 |
|anne |3.3333 |
|alex |6.0000 |
|tommy |9.4166 |
|bob |0.5833 |
|buddy |4.2500 |
+--------------------+
注意:仅当 age 列中有两个数字时,以上答案才有效。
Note: The above answer works only when there are two numbers in the age column.
这篇关于将Varchar转换为Float / double的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!