删除SQL Select语句中的非数字值(Remove Non Numeric Values Within SQL Select Statement)

删除SQL Select语句中的非数字值(Remove Non Numeric Values Within SQL Select Statement)

我在数据库列VALUE中有一个值：

C_4327

我需要从中删除非数字文本，以便它只留下数字。 我尝试在SQL中使用REPLACE函数，但不是我不想替换，只是将它们删除。 我之前使用PHP来执行此操作：

preg_replace("/[^0-9]/","",$row['VALUE']);

我正在检索SELECT语句中的值。

非常感谢您的帮助。

提前致谢

I have a value in a database column VALUE:

C_4327

I need to strip the non numeric text from this so that it just leaves the numbers. I have tried using the REPLACE function within SQL but not I don't want to replace, just strip them out. I was previously using PHP to do this:

preg_replace("/[^0-9]/","",$row['VALUE']);

I'm retrieving the value in a SELECT statement.

Your help would be appreciated.

Thanks in advance

最满意答案

如果要获取字符串末尾的数字，可以使用以下神秘方法：

select reverse(reverse(value) + 0) as NumberAtEnd;

在你的情况下：

value ='C_4327' reverse(value) ='7234_C' reverse(value) + 0 = 7234 reverse(reverse(value) + 0) ='4327'

If you want to get the number at the end of the string, you can use the following arcane approach:

select reverse(reverse(value) + 0) as NumberAtEnd;

In your case:

value ='C_4327' reverse(value) = '7234_C' reverse(value) + 0 = 7234 reverse(reverse(value) + 0) = '4327'