SQL 只从 varchar 中获取数值 [英] SQL take just the numeric values from a varchar

问题描述

假设我有几个字段，如下所示:

Say i have a few fields like the following:

abd738927
jaksm234234
hfk342
ndma0834
jon99322

类型:varchar.

Type: varchar.

我如何只从中获取数值来显示:

How do I take just the numeric values from this to display:

738927
234234
342
0834
99322

尝试过 substring 但是数据长度不同，并且由于无法转换而无法转换，有什么想法吗?

Have tried substring however the data varies in length, and cast didnt work either due to being unable to convert, any ideas?

推荐答案

以下是 PATINDEX 的示例:

Here's the example with PATINDEX:

select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))

这假设 (1) 字段将有一个数字，(2) 数字都组合在一起，以及 (3) 数字后面没有任何后续字符.

This assumes (1) the field WILL have a numeric, (2) the numerics are all grouped together, and (3) the numerics don't have any subsequent characters after them.

这篇关于SQL 只从 varchar 中获取数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！