从Teradata中的列过滤不需要的字符

发布于 2025-02-07 05:37:34 字数 242 浏览 2 评论 0原文

我的表中有一个电话号码列，值仅是数字，没有特殊字符。对于其中一列，我获得了一个值为“：1212121212”。

我将需要过滤此记录，并在Teradata中带有任何特殊字符的任何记录。谁能为此提供帮助。

我尝试了以下解决方案，但它不起作用

where (REGEXP_SUBSTR(column_name, '[0-9]+')<>1 or column_name is null )

原文

I have a Phone number column in my table with values only being numbers and no special characters. for one of the column I got a value coming in as ":1212121212".

I will need to filter this record and any records coming in with any special characters in teradata. Can anyone help on this.

I have tried the below solutions but it is not working

where (REGEXP_SUBSTR(column_name, '[0-9]+')<>1 or column_name is null )

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我恋#小黄人 2025-02-14 05:37:34

在MS SQL Server db中，您可以使用trycast找到具有非数字字符的条目：

SELECT column_name 
FROM yourtable
WHERE TRY_CAST(column_name AS INT) IS NULL;

在Teradata db中，您可以使用to_number：

SELECT column_name 
FROM yourtable
WHERE TO_NUMBER(column_name) IS NULL;

如果要保持靠近您的尝试，可以使用喜欢找到未数字条目：

SELECT column_name 
FROM yourtable
WHERE column_name LIKE '%[^0-9]%';

请注意，当您的表有很多行时，这可能会变得慢。

In MS SQL Server DB's, you can use TRYCAST to find those entries having non numeric characters:

SELECT column_name 
FROM yourtable
WHERE TRY_CAST(column_name AS INT) IS NULL;

In Teradata DB's, you can use TO_NUMBER:

SELECT column_name 
FROM yourtable
WHERE TO_NUMBER(column_name) IS NULL;

If you want to stay close to your attempt, can use LIKE to find not numeric entries:

SELECT column_name 
FROM yourtable
WHERE column_name LIKE '%[^0-9]%';

Note this could get slow when your table has very many rows.

回复收藏 0 原文

好听的两个字的网名 2025-02-14 05:37:34

谢谢乔纳斯。由于我只需要数字值，而长度应为10，因此我尝试了以下操作，并且它起作用。这将忽略所有其他特殊字符。

(regexp_similar(Column,'[0-9]{10}')=1)

Thanks Jonas. Since I need only numeric values and the length should be 10, I tried the below and it worked. This would ignore all the additional special characters.