Replace numbers with special characters in sql string

Question

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 6-Oct-13 21:59pm

mimtiyaz

Add a Solution

Comments

Azee 7-Oct-13 4:14am

SQL SERVER, MYSQL? what are you using and please specific the functions that you tried.

2 solutions

Solution 2

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 *@#

Posted 6-Oct-13 22:45pm

Raja Sekhar S

Updated 6-Oct-13 22:56pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Madhu Nair · Accepted Answer · 2013-10-06T22:28:00

Solution 1

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

Posted 6-Oct-13 22:28pm

Madhu Nair

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.

mimtiyaz 8-Oct-13 5:27am

Hi. I made some modifications in the script. I changed special characters with arabic numbers. Its working fine, but when i'm providing some special characters with the string, I'm getting an error. For eg : if I provide an arabic date 01/12/1434 I'm getting error due to special character. Can you please fix this issue ..
Thanks

Madhu Nair 8-Oct-13 5:31am

post your latest queries.... I will try

mimtiyaz 8-Oct-13 5:37am

Actually there is a problem posting the query. Can you provide me your email id so that i can post it.

Thanks

Madhu Nair 8-Oct-13 5:39am

madhusasinair@gmail.com

mimtiyaz 8-Oct-13 5:44am

Please check your email.

mimtiyaz 8-Oct-13 6:28am

Thanks Madhu. It worked.

mimtiyaz 7-Oct-13 6:23am

conversion from varchar to int error..

Madhu Nair 7-Oct-13 6:26am

can you post the query you are using? maybe i can check it

mimtiyaz 7-Oct-13 6:28am

The query which you gave me, I'm using that.

mimtiyaz 7-Oct-13 6:29am

input value : شصث123هشسيب

gvprabu 7-Oct-13 7:28am

good work....