Geek Answers Handbook

Removing numbers from a string in mysql

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

  Season
 ------
 FALL01
 FALL05
 Spring01
 Summer06
0
source share
5 answers

I suggest you manually create a User Define Function for this. Here is a great tutorial 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, now you can easily remove the numbers from the string, for example

 SELECT uExtractNumberFromString(Season) FROM TableName
+4
source

If your data has the last 2 characters as numbers, you can use

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

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

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

Basically, it changes the string, detects the length of the numeric postfix (which becomes the prefix when it is reversed), converting the reverse string to int, then takes N leftmost characters from the original string, where N is the length of the string minus the numeric length of the postfix. Additional "1" and + 1 are needed to account for lines ending in 0, and to process lines without tail numbers.

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

0
source

If you do not want to write your own stored function for this, it is quite simple using the replace_all() function in common_schema .

For example, this will remove all occurrences 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
0
source

If you know that the numbers will be at the beginning of the line, you can use the ASCII() function and check the ASCII ranges for the numbers.

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() returns only the ASCII value of the first digit of the string, and then SUBSTRING_INDEX() used to retrieve everything after the first space character.

0
source

More articles:


All Articles