如何只获取行中的数值? [英] How to get only numerical values in rows?
本文介绍了如何只获取行中的数值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含几行的表格,如
栏
abc1
23abc
4nm
我想要这样的输出
栏
1
23
4
只有数值
我尝试过:
select isnumeric(string)
但它不会帮助我..
请帮帮我
I have one table containing few rows like
column
abc1
23abc
4nm
I want output like this
column
1
23
4
only numerical values
What I have tried:
select isnumeric(string)
but it is not going to help me out..
please help me on this
推荐答案
这是一个糟糕的主意,因为每次执行它会占用大量的服务器资源:如果你有大量行(或经常这样做),你将浪费大量的服务器处理时间! />
我建议您分配第二列,该列仅包含数值,并在插入另一列时插入,或者在您的表示语言中执行此操作,其中一个简单的正则表达式将执行此操作并分发升在不同的机器上。
但是......你可以这样做: tsql - 用于删除非数字字符的T-SQL选择查询 - Stack Overflow [ ^ ] - 请参阅eDriven_Levar在底部。
我真的不推荐它。
It's a poor idea, because it's going to take significant server resources each time you do it: If you have a large number of rows (or do this frequently) you are going to waste a lot of Server processing time!
I'd recommend that either you allocate a second column which holds only the numeric values and insert that when you insert the other column, or do this in your presentation language where a simple regex will do it and distribute the load across different machines.
But...you can do it: tsql - T-SQL select query to remove non-numeric characters - Stack Overflow[^] - see the SQL function given by eDriven_Levar at the bottom.
I really don't recommend it though.
例如,如果你的表名是tbltest而列名是columntest,你的查询将如下,
SELECT LEFT(子步,PATINDEX('%[^ 0-9]%',subrt +'t') - 1)
FROM(
SELECT subsrt = SUBSTRING(columntest,pos,LEN(columntest))
FROM(
SELECT columntest,pos = PATINDEX ('%[0-9]%',columntest)
FROM tbltest
)d
)t
For example if your table name is tbltest and column name is columntest, your query will be like following,
SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)
FROM (
SELECT subsrt = SUBSTRING(columntest, pos, LEN(columntest))
FROM (
SELECT columntest, pos = PATINDEX('%[0-9]%', columntest)
FROM tbltest
) d
) t
这篇关于如何只获取行中的数值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文