SQL仅从一个varchar获取数值。

Say i have a few fields like the following:

假设我有以下几个字段:

abd738927
jaksm234234
hfk342
ndma0834
jon99322

Type: varchar.

类型:varchar。

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

我如何从这里取数值来显示:

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

已经尝试过子字符串，但是数据的长度不同，而且cast也不能转换，有什么想法吗?

8 个解决方案

#1

Here's the example with PATINDEX:

下面是PATINDEX的例子:

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

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.

这假设(1)字段将有一个数字，(2)所有数字都分组在一起，(3)数字后面没有任何后续字符。

#2

Well if you don't want to create a function, you can just something like this:

如果你不想创建一个函数，你可以这样:

cast(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(YOUR_COLUMN
,'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J','')
,'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T','')
,'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),'$',''),',',''),' ','') as float)

#3

I think you're wanting VBA's Val() function. Easy enough to accomplish with IsNumeric()

我认为您需要VBA的Val()函数。使用IsNumeric()很容易实现

create function Val 
(
    @text nvarchar(40)
) 
returns float
as begin
    -- emulate vba's val() function
    declare @result float 
    declare @tmp varchar(40)

    set @tmp = @text
    while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
        set @tmp=left(@tmp,len(@tmp)-1)
    end
    set @result = cast(@tmp as float)

    return @result
end

#4

DECLARE @NonNumeric varchar(1000) = 'RGI000Testing1000'
DECLARE @Index int  
SET @Index = 0  
while 1=1  
begin  
    set @Index = patindex('%[^0-9]%',@NonNumeric)  
    if @Index <> 0  
    begin  
        SET @NonNumeric = replace(@NonNumeric,substring(@NonNumeric,@Index, 1), '')  
    end  
    else    
        break;   
end     
select @NonNumeric -- 0001000

#5

Extract only numbers (without using while loop) and check each and every character to see if it is a number and extract it

只提取数字(不使用while循环)，并检查每个字符，看它是否是一个数字并提取它

   Declare @s varchar(100),@result varchar(100)
    set @s='as4khd0939sdf78' 
    set @result=''

    select
        @result=@result+
                case when number like '[0-9]' then number else '' end from 
        (
             select substring(@s,number,1) as number from 
            (
                select number from master..spt_values 
                where type='p' and number between 1 and len(@s)
            ) as t
        ) as t 
    select @result as only_numbers

#6

input table

输入表

if you have data like above in the image, then use the below query

如果图像中有如上所示的数据，那么使用下面的查询

select field_3 from table where PATINDEX('%[ ~`!@#$%^&*_()=+\|{};",<>/?a-z]%', field_3)=0

Results will be look like this

结果是这样的

Result table

结果表

#7

select substring(
                'jaksm234234',
                patindex('%[0-9]%','jaksm234234'),
                LEN('jaksm234234')-patindex('%[0-9]%','jaksm234234')+2
                )

#8

Extract only numbers from a string. Returns a string with all the numbers inside. Example: this1is2one345long6789number will return 123456789

只从字符串中提取数字。返回包含所有数字的字符串。示例:this1is2one345long6789number将返回123456789

CREATE FUNCTION [dbo].[GetOnlyNumbers] (@Temp VARCHAR(1000))

RETURNS VARCHAR (1000) AS BEGIN

返回VARCHAR (1000)

    DECLARE @KeepValues AS VARCHAR(50)
    SET @KeepValues = '%[^0-9]%'
    WHILE PATINDEX(@KeepValues, @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END

#1