CODEWITHSUNDEEP
mysql
user3379655
user3379655

Reputation: 258

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

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.

Upvotes: 0

Views: 126

Answers (2)

Barmar
Barmar

Reputation: 782717

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

Upvotes: 3

Leon
Leon

Reputation: 745

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 %

Upvotes: -1

Related Questions