Click here to Skip to main content
15,795,789 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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.
Posted
Comments
Azee 7-Oct-13 4:14am    
SQL SERVER, MYSQL? what are you using and please specific the functions that you tried.

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 -

SQL
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
 
Share this answer
 
Comments
mimtiyaz 7-Oct-13 5:54am    
This function is working fine when input alphanumeric values only. If I place any special character within the string, I'm getting an error.
For eg : hello / world 123 *
Madhu Nair 7-Oct-13 6:03am    
I am not getting any error on my end!!!!

Are you still having problem?
mimtiyaz 7-Oct-13 6:22am    
yes I do have..
try with some ramdom values..
Actually I'm providing arabic letters and numbers
Madhu Nair 7-Oct-13 6:25am    
I have tested with all the special characters and in English.

Arabic, I don't have any idea as i don't have it installed on my system
mimtiyaz 7-Oct-13 6:27am    
I'll try to fix this issue.. Anyways, thanks Madhu.
u can try this...
SQL
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:
C#
hello #%& world *@#
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900