我想使用sql语句从字符串中删除数字值[不使用特殊关键字] [英] I want to remove the numeric values from string using sql statement [without using special keywords]

问题描述

以下是要解决的答案:

Below is the answer to resolve:

<pre lang="SQL">
Declare @str varchar(100)
Declare @ln INT
Declare @cnt INT
Declare @sts INT
Declare @stsinsrt INT
set @stsinsrt=1
set @cnt=0
Declare @curchr varchar(100)
Declare @finalstr varchar(100)
set @str=''dsfdsf43543543sfas34534asf4354 Rajnish375747893897389538475''

set @ln=(select LEN(@str))
DECLARE @tmp TABLE 
(
res varchar(100)
)
while (@cnt<@ln)
begin
set @curchr =(select SUBSTRING(@str,@cnt,1))
set @sts=(SELECT ISNUMERIC(@curchr))

if(@sts=0)
begin
if(@stsinsrt=1)
begin
set @stsinsrt=0
insert into @tmp values(@curchr)
end
else
update @tmp set res=res+@curchr
end
set @cnt=@cnt+1

end

SELECT *
FROM @tmp

推荐答案

嗨..
参见下面的代码...
它将在单个变量中返回所有同数值，而不在另一个变量中返回同数值..

Hi..
See the below code...
it will return you all isnumeric value in single variable and not isnumeric value in another variable..

declare @str varchar(100)
declare @cnt int
declare @letter int
declare @nstr varchar(100) 
declare @vstr varchar(100) 
set @vstr=''
set @nstr=''
set @str ='dsfdsf43543543sfas34534asf4354 Rajnish375747893897389538475'
set @cnt = 1
while @cnt < len(@str)
begin
 select @letter = isnumeric(substring(@str,@cnt,1))
	if @letter = 1
	  begin 
	    set @nstr=@nstr+substring(@str,@cnt,1) 
	  end 
	else
	  begin 
	    set @vstr=@vstr+substring(@str,@cnt,1) 
	  end
  set @cnt=@cnt+ 1
end
print @nstr  -- return isnumeric value
print @vstr  -- returns not isnumeric value

这篇关于我想使用sql语句从字符串中删除数字值[不使用特殊关键字]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！