How to replace numeric character with "%" in MySQL?

Question

How to replace numeric character with "%" in MySQL ?

i.e : abcd 1234 zz => abcd %%%% zz

What I have :

Declare cursorVideoTitles cursor  for select temp_playList.VideoTitle 
      from temp_playList  where temp_playList.VideoTitle regexp '^[A-Za-z0-9]+$';

cursorVideoTitles contain all alphanumeric video titles. now after this I need to replace numeric character with "%"

Reason for this : I need to search it in other table which have same alphabets. so 1st I take all those values in cursor variable and iterate it with like query,so i will get matching records.

Barmar · Accepted Answer · 2014-03-19T15:50:34.663

3

Unfortunately, MySQL doesn't have a regular expression replace function, you have to replace each digit separately.

SELECT
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(VideoTitle, '0', '%'),
                                        '1', '%'),
                                    '2', '%'),
                                '3', '%'),
                            '4', '%'),
                        '5', '%'),
                    '6', '%'),
                '7', '%'),
            '8', '%'),
        '9', '%') AS TitleMasked

edited Mar 19 '14 at 15:50

answered Mar 19 '14 at 15:35

Barmar

741,623
53
500
612

1

where there is a will, there is a way :) – edmondscommerce Mar 19 '14 at 15:36
In all that crazy mayhem there's a missing `'` on the inner-most `REPLACE`, but this is how it's done. – tadman Mar 19 '14 at 15:48
Please see this link : I hope u can give me some idea for this issue too.. : http://stackoverflow.com/questions/22505494/how-to-get-records-which-contain-alphanumeric-characters-white-spaces/22506120?noredirect=1#22506120 – user3379655 Mar 20 '14 at 08:06

score -1 · Answer 2 · answered Mar 19 '14 at 15:32

-1

The easiest thing I can think is something like:

SELECT REPLACE(‘abcd1234zz’, ‘1234’, ‘%%%%’);

This query looks if there are 1234 in a value and replace them with four %

answered Mar 19 '14 at 15:32

Leon

665
4
10
32

1

He needs to replace all digits, not specifically `1234`. That was juset an example. – Barmar Mar 19 '14 at 15:33
Oh ok. Well it's the same. He just has to reiterate it for every digit :) – Leon Mar 19 '14 at 15:37
Like my answer, you mean? – Barmar Mar 19 '14 at 15:37
yes sir, thank you for your priceless code.. :) @ Barmar – user3379655 Mar 20 '14 at 08:03

How to replace numeric character with "%" in MySQL?

2 Answers2