Removing numbers from string in mysql

Question

I have a string like "FALL01" and i have to remove number from such string so output should look like Fall , spring and the like. Kindly let me know how can i remove number with sql query . Following is my sample table. Thanks

Season
------
FALL01
FALL05
Spring01
Summer06

It would be much easier to do it after fetching from database. Can you? — John Dvorak, Dec 13 '12 at 07:32
It's very easy in any environment that has a regex replace. PHP has, C# has, MySQL doesn't. — John Dvorak, Dec 13 '12 at 07:34
are the two last character constant? or you have strings like this `FALL123`, `HELLO2222`? — John Woo, Dec 13 '12 at 07:36
@KuyaJohn yes you're right , I have strings like this FALL123, HELLO2222 — Naqash Malik, Dec 13 '12 at 07:55

score 5 · Answer 1 · answered Jan 27 '23 at 17:35

5

REGEXP_REPLACE()

Example:

UPDATE example_table
SET Season = REGEXP_REPLACE(Season, '[0-9]+', '');

This replaces any number in the Season column with an empty string.

answered Jan 27 '23 at 17:35

bubbzDotDev

51
1
5

score 3 · Accepted Answer · answered Dec 13 '12 at 08:15

I suggest the you manually create a User Define Function for this. Here's a great tutorial that you can use

Extract numbers out of a string

Code Snippet:

DELIMITER $$ 

DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) 
RETURNS INT
NO SQL

BEGIN

    DECLARE ctrNumber varchar(50);
    DECLARE finNumber varchar(50) default ' ';
    DECLARE sChar varchar(2);
    DECLARE inti INTEGER default 1;

    IF length(in_string) > 0 THEN

        WHILE(inti <= length(in_string)) DO
            SET sChar= SUBSTRING(in_string,inti,1);
            SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');

            IF ctrNumber > 0 THEN
               SET finNumber=CONCAT(finNumber,sChar);
            ELSE
               SET finNumber=CONCAT(finNumber,'');
            END IF;
            SET inti=inti+1;
        END WHILE;
        RETURN CAST(finNumber AS SIGNED INTEGER) ;
    ELSE
        RETURN 0;
    END IF;

END$$

DELIMITER ;

once the function has been created, you can now easily remove the numbers from string, example

SELECT uExtractNumberFromString(Season)
FROM   TableName

This solution returns only the numbers, whereas OP asked for everything BUT the numbers. https://stackoverflow.com/a/60722518/3907405 is a modification of this answer with the correct solution. — Brad, Mar 17 '20 at 12:34

Ike Walker · Answer 3 · 2017-06-05T14:50:12.430

3

If you don't want to write your own stored function to do this it's pretty simple using the replace_all() function in common_schema.

For example, this will remove all occurences of the digits 0-9 from the values in the Season column of your table:

select common_schema.replace_all(season,'0123456789','')
from your_table

edited Jun 05 '17 at 14:50

answered Dec 11 '13 at 00:28

Ike Walker

64,401
14
110
109

1

Error:`FUNCTION common_schema.replace_all does not exist` – Sandeep Kumar May 31 '17 at 09:49
2

@SandeepKumar You first need to download and install common_schema. It's available at https://github.com/shlomi-noach/common_schema – Ike Walker Jun 05 '17 at 14:50

score 2 · Answer 4 · answered Dec 13 '12 at 07:35

2

If your Data has last 2 characters as digits then you can use

select substr(season,1,length(season)-2) from tbl;

answered Dec 13 '12 at 07:35

Ajith Sasidharan

1,155
7
7

this works if the last two characters is constant. but this may fail if you have string like this `FALL123`. – John Woo Dec 13 '12 at 07:37
@Kuya John Completely agree on that part!! – Ajith Sasidharan Dec 13 '12 at 07:40
I need a generic solution that works for all number lengths and variations – Naqash Malik Dec 13 '12 at 07:49

weirdan · Answer 5 · 2013-10-22T16:28:56.367

Assuming @str is the value you want to strip tail numbers from, the following expression will do what you need:

left( @str, length(@str) - length( reverse( concat(@str, "1") ) + 0 ) + 1 );

Basically, it reverses the string, finds out the length of numeric postfix (which becomes prefix when reversed) by converting reversed string to int, then takes N leftmost characters from original string where N is string length minus numeric postfix length. Additional "1" and + 1 is needed to account for strings ending with 0 and to handle strings with no tail numbers.

I believe using this casting trick will only work for MySQL.

score 2 · Answer 6 · answered Mar 17 '20 at 12:32

A modification of the UDF suggested by John Woo.

DELIMITER $$ 

DROP FUNCTION IF EXISTS `uExtractNonNumbersFromString`$$
CREATE FUNCTION `uExtractNonNumbersFromString`(in_string varchar(50)) 
RETURNS text
NO SQL

BEGIN

    DECLARE ctrNumber varchar(50);
    DECLARE finText text default ' ';
    DECLARE sChar varchar(2);
    DECLARE inti INTEGER default 1;

    IF length(in_string) > 0 THEN

        WHILE(inti <= length(in_string)) DO
            SET sChar= SUBSTRING(in_string,inti,1);
            SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');

            IF ctrNumber = 0 THEN
               SET finText=CONCAT(finText,sChar);
            ELSE
               SET finText=CONCAT(finText,'');
            END IF;
            SET inti=inti+1;
        END WHILE;
        RETURN finText;
    ELSE
        RETURN '';
    END IF;

END$$

DELIMITER ;

Create the above function and then call as

SELECT uExtractNonNumbersFromString(Season)
FROM   TableName

Suggest changing varchar(50) to varchar(255) or this fails with "Error Code: 1406 Data too long for column 'in_string' at row xxxx" on any string longer than 50 characters. — Ted Scheckler, Aug 20 '23 at 14:02

score 0 · Answer 7 · answered Apr 14 '16 at 22:11

If you know the numbers will be at the start of the string you can use the ASCII() function and check for the ASCII ranges for digits.

For example this will remove house numbers from addresses:

SELECT IF(ASCII(address) BETWEEN 48 AND 57, substring_index(address, ' ', -2), address)
FROM user;

ASCII() only returns the ASCII value of the first digit of the string and then SUBSTRING_INDEX() is used to fetch everything after the first space character.

Julian Cochran · Answer 8 · 2022-12-22T13:49:35.593

0

No stored procedure is required.

Select replace(replace(replace(col, ‘1’, ‘’), ‘2’, ‘’), ‘3’, ‘’) from table

Expand to include all digits 0 to 9.

edited Dec 22 '22 at 13:49

answered Dec 22 '22 at 13:48

Julian Cochran

1
1

Removing numbers from string in mysql

8 Answers8

Linked