SQL Server的isNumeric()等效于Amazon Redshift [英] SQL Server's isNumeric() equivalent in amazon redshift

问题描述

• 我正在使用Amazon redshift作为我的数据仓库
• 我有一个字符串类型的字段(field1).有些字符串以四个数字开头，而其他的则以字母开头:

'test alpha'
"1382测试版"

'test alpha'
'1382 test beta'

• 我想过滤掉字符串不以四个数字开头的行
• 看着redshift文档，我不相信isnumber或isnumeric是函数.似乎赞"功能是最有可能的.

• 我尝试了

• I want to filter out rows where the string does not start with four numbers
• Looking at the redshift documentation, I don't believe isnumber or isnumeric are functions. It seems that the 'like' function is the best possibility.

• I tried

左边的(field1，4)像'[0-9] [0-9] [0-9] [0-9]'

where left(field1, 4) like '[0-9][0-9][0-9][0-9]'

这不起作用，并且从下面的链接看来，redshift可能不支持这一点:

this did not work and from the link below seems like redshift may not support that:

https://forums.aws.amazon.com/message.jspa? messageID = 439850

"where"子句中是否有错误?如果不支持，并且redshift不支持该子句，是否可以过滤?我当时在考虑使用演员表

is there an error in the 'where' clause? if not and that clause isn't supported in redshift, is there a way to filter? I was thinking of using cast

cast(left(field1,4) as integer) 

，如果它生成错误，则越过该行，但不确定如何在Amazon redshift中执行此操作.或是否有其他代理用于等值数字过滤器.

and then passing over the row if it generated an error, but not sure how to do this in amazon redshift. or is there some other proxy for the isnumeric filter.

谢谢

推荐答案

似乎redshift不支持以下任何一项:

It seems that redshift doesn't support any of the following:

where left(field1,4) like '[0-9][0-9][0-9][0-9]' 
where left(field1,4) ~ '^[0-9]{4}'
where left(field1,4) like '^[0-9]{4}'

似乎有效的是:

where left(field1,4) between 0 and 9999

这将返回以四个数字字符开头的所有行.

this returns all rows that start with four numeric characters.

看来，即使field1是字符串类型，当字符串字符为数字时，之间"函数也将left(field1,4)解释为单个整数(如果它们不是数字，则不会给出错误).如果发现问题，我会跟进.例如，我不处理任何小于1000的东西，因此我假设但不确定将0001解释为1.

it seems that even though field1 is type string, the 'between' function interprets left(field1,4) as a single integer when the string characters are numeric (and does not give an error when they are not numeric). I'll follow up if I find a problem. For instance I don't deal with anything less than 1000, so I assume, but am not sure, that 0001 is interpreted as 1.

这篇关于SQL Server的isNumeric()等效于Amazon Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！