从值中选择数字部分 [英] Select number part from a value

问题描述

我在表中有一个字符串字段，其中包含100S，200Mg，5Yrs之类的值.如何使用select语句仅从此字段中取出数字部分.

I have a string field in a table that conatains values like 100S,200Mg,5Yrs. How can I take only the number part from this field by using a select statement.

推荐答案

尝试此

try this

DECLARE @NumStr varchar(1000)
SET @NumStr = '100S';
BEGIN
WHILE PATINDEX('%[^0-9]%',@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')
END
PRINT @NumStr

Declare @S As VarChar(15)
Set @S = '   5000.00/- RS'
Select Right(@S, 2) As Rupees, Left(@S, 10) As Amount
Select Substring(@S, 14, 2) As Rupees, Substring(@S, 1, 13) As Amount

您可以尝试像这样

You can try like that

using System.Text.RegularExpressions;

string s = "100S,200Mg,5Yrs";
Regex r = new Regex("[a-zA-Z]");
s = r.Replace(s, "");

输出将是

output would be

100,200,5

我只是用空格替换所有字符，如果您还有其他字符，则创建自己的RegEx.

I am just replacing all the charcters with space, if you have some other char then create your own RegEx.

这篇关于从值中选择数字部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！