更新时间:2023-12-05 12:54:28
如果您使用的是sql server,请尝试下面给出的代码。您可以使用以下代码创建函数,并在查询中使用它。尝试&如果找到相同的范围,请改进代码 -声明 @ str varchar ( 100 )= ' mad123hu'
声明 @ i int = 1
while @ i< = len( @ str )
开始
声明 @ val varchar ( 1 )
set @ val = substring( @ str , @i , 1 )
if ascii( @ val )> = 48 和 ascii( @ val )< = 57
开始
声明 @ newchar varchar ( 1 )
set @ newchar = case ascii( @ val )
何时 48 然后 ' !'
49 然后 ' @'
when 50 ' #'
51 然后 '
52 then ' %'
when 53 ' ^'
54 然后 ' *'
when 55 然后 ' ('
56 然后 ' )'
当 57 然后 ' _'
end
set @ str = replace( @ str , @ val , @ newc har )
end
set @ i + = 1
end
选择 @ str
你可以试试这个...声明 @ s varchar ( 100 ), @ result varchar ( 100 )
set @s = ' hello 123 world 456'
set @ result = ' '
选择 @ result = @ result + 案例 当号码喜欢 ' [0-9]' 然后
案例 当号码 in (' 0',' 5')然后 ' @'
其他 案例 编号 (' 1',' 6')然后 ' #'
其他 案例 当编号 (' 2',' 7')然后 ' %'
其他 案例 编号 (' 3',' 8')然后 ' &'
其他 ' *'
结束
结束
结束
结束
其他号码
结束
From ( select substring( @ s ,number, 1 ) as number
来自(选择号码来自 master..spt_values
其中 type = ' p' 和 number 1 和 len( @ s )) as t
) as t
选择 @ result ;
输出:你好#%&世界* @#
I've a string in sql which contains alphanumeric value (eg: hello 123 world 456).
I want to replace numeric values i.e., 123465 with some special characters like @#%&*^ respectively. I tried to script many function but I'm not getting exactly what I want.
Suggestions will be appreciated.
Thanks.
if you are using sql server then try the given below code. You can create a function using the below code and use it in your queries. Try & improve the code if you find any scope for the same -declare @str varchar(100)='mad123hu' declare @i int=1 while @i<=len(@str) begin declare @val varchar(1) set @val=substring(@str,@i,1) if ascii(@val) >=48 and ascii(@val)<=57 begin declare @newchar varchar(1) set @newchar=case ascii(@val) when 48 then '!' when 49 then '@' when 50 then '#' when 51 then '
' when 52 then '%' when 53 then '^' when 54 then '*' when 55 then '(' when 56 then ')' when 57 then '_' end set @str=replace(@str,@val,@newchar) end set @i+=1 end select @str
u can try this...Declare @s varchar(100),@result varchar(100) set @s='hello 123 world 456' set @result='' Select @result = @result + Case When number like '[0-9]' Then Case When number in('0','5') Then '@' Else Case When number in('1','6') Then '#' Else Case When number in('2','7') Then '%' Else Case When number in('3','8') Then '&' Else '*' End End End End Else number End From (select substring(@s,number,1) as number from (select number from master..spt_values where type='p' and number between 1 and len(@s)) as t ) as t select @result;
Output:hello #%& world *@#