如何从表的字符串varchar字段中获取数值 [英] How to get numeric value from a string varchar field of Table

问题描述

嗨..朋友，我有一个带有列名称代码的表，但是问题是，根据我的要求，我必须在其中存储值，例如C-1，CD-2，C-3，D4，C5 ... ..

我希望当我选择记录时，它仅返回数字值，并且除字符和(-)之外，结果看起来像1,2,3,4,5，请帮助我

Hi.. friend I have a table having column name code but the problem is that according to my requirement i have to store values in it like C-1,CD-2,C-3,D4,C5.....

I want that when I will select the record then it returns only the Numeric values and except the character and (-) sign the result look like 1,2,3,4,5 please help me

推荐答案

获得结果后，请做一件事

字符串变量="c-1"; //这是您从数据库中获得的值
int strtindex = variable.IndexOf(''-'');
variable = variable.Substring(variable + 1);


现在变量包含的值不包含字符和连字符


如果正确，请发表评论


从数据库中直接获取结果
之后，实现此功能

after getting the result do one thing

string variable="c-1"; //this is the value u got from database
int strtindex = variable.IndexOf(''-'');
variable= variable.Substring(variable + 1);


now variable contains values free from character and hyphen


leave a comment if correct


get the result from the database as it is
after that implement this function

Hello Dear，

请在存储过程中尝试以下代码，以摆脱字符串中的字符值.

声明@l_column_value varchar(max)
设置@l_column_value =``C-1''
设置@ l_column_value = SUBSTRING(@ l_column_value，CHARINDEX(''''，@ l_column_value)+1,100)
打印@l_column_value

在上面的代码中，子字符串方法的第三个参数表示您要在-"之后检索的字符数.根据上面的代码，它将在-"之后检索100个字符.

如果值得的话，请发表评论.

Hello Dear,

Please try the below code in your stored procedure to get rid of the character value from the string.

declare @l_column_value varchar(max)
set @l_column_value = ''C-1''
set @l_column_value=SUBSTRING(@l_column_value,CHARINDEX('''',@l_column_value)+1,100)
print @l_column_value

In the above code the third parameter of the substring method indicates the number of character you want to retrieve after ''-''. According to above code it is going to retrieve 100 characters after ''-''.

Please leave a comment if it is worthy to you.

select CODE, substring(
CODE,
patindex(''%[0-9]%'',CODE),
datalength(CODE)-patindex(''%[0-9]%'',
reverse(CODE))-patindex(''%[0-9]%'',CODE)+2
) as amount
from
MASTER 
where
CODE like ''%[0-9]%''

其中表名称为MASTER ，字段名称为CODE

Where table name is MASTER and field name is CODE

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