CODEWITHSUNDEEP
mysql
Naqash Malik
Naqash Malik

Reputation: 1816

Removing numbers from string in mysql

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

Upvotes: 7

Views: 29345

Answers (8)

bubbzDotDev
bubbzDotDev

Reputation: 81

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.

Upvotes: 8

Julian Cochran
Julian Cochran

Reputation: 1

No stored procedure is required.

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

Expand to include all digits 0 to 9.

Upvotes: 0

Brad
Brad

Reputation: 306

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

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65537

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

Upvotes: 3

Mikey
Mikey

Reputation: 33

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.

Upvotes: 0

weirdan
weirdan

Reputation: 2632

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.

Upvotes: 2

John Woo
John Woo

Reputation: 263693

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

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

Upvotes: 3

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

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

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

Upvotes: 2

Related Questions