有个朋友遇到个问题,他问怎么样把包含特殊符号的值取出来
字段里包含 6956@1263.。或者698465#221.。或者69856中4334..
意思是数字中存在非数字符号或者文字之类的,如何用SQL语句查询出来,PS非法字符不知道是哪个
DECLARE @position int, @string char(15),@t_num varchar(50)
DECLARE cursor_r cursor for select t_num from tab2
open cursor_r
fetch next from cursor_r into @string
begin
while @@fetch_status =0
begin
--print @string
SET @position = 1
WHILE @position <= len(@string)
BEGIN
set @t_num =ASCII(SUBSTRING(@string, @position, 1))
if @t_num<48 or @t_num>57
begin
insert into tab3 (t_num) values(@string)
break
end
SET @position = @position + 1
END
fetch next from cursor_r into @string
end
close cursor_r
deallocate cursor_r
end
select * from tab3