blocks|key|818803|text|从性能的角度来看，(并且假设你读的比写的多)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|818804|我认为最好的方法是预先计算并存储列的剥离版本，这样可以减少转换。|818805|然后，您可以在新列上建立索引，并让数据库为您完成这项工作。|818806|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

From a performance point of view,
(and on the assumption that you read more than you write)

I think the best way would be to pre calculate and store a stripped version of the column, 
This way you do the transform less.

You can then put an index on the new column and get the database to do the work for you.

blocks|key|818949|text|我已经写了这个UDF。但是，它只修剪字符串开头的特殊字符。它还将字符串转换为小写。如果需要，您可以更新此函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|818950|DELIMITER+//

DROP+FUNCTION+IF+EXISTS+DELETE_DOUBLE_SPACES//

CREATE+FUNCTION+DELETE_DOUBLE_SPACES+(+title+VARCHAR(250)+)
RETURNS+VARCHAR(250)+DETERMINISTIC
BEGIN
++++DECLARE+result+VARCHAR(250);
++++SET+result+=+REPLACE(+title,+'++',+'+'+);
++++WHILE+(result+<>+title)+DO+
++++++++SET+title+=+result;
++++++++SET+result+=+REPLACE(+title,+'++',+'+'+);
++++END+WHILE;
++++RETURN+result;
END//

DROP+FUNCTION+IF+EXISTS+LFILTER//

CREATE+FUNCTION+LFILTER+(+title+VARCHAR(250)+)
RETURNS+VARCHAR(250)+DETERMINISTIC
BEGIN
++++WHILE+(1=1)+DO
++++++++IF(++ASCII(title)+BETWEEN+ASCII('a')+AND+ASCII('z')
++++++++++++OR+ASCII(title)+BETWEEN+ASCII('A')+AND+ASCII('Z')
++++++++++++OR+ASCII(title)+BETWEEN+ASCII('0')+AND+ASCII('9')
++++++++)+THEN
++++++++++++SET+title+=+LOWER(+title+);
++++++++++++SET+title+=+REPLACE(
++++++++++++++++REPLACE(
++++++++++++++++++++REPLACE(
++++++++++++++++++++++++title,
++++++++++++++++++++++++CHAR(10),+'+'
++++++++++++++++++++),
++++++++++++++++++++CHAR(13),+'+'
++++++++++++++++)+,
++++++++++++++++CHAR(9),+'+'
++++++++++++);
++++++++++++SET+title+=+DELETE_DOUBLE_SPACES(+title+);
++++++++++++RETURN+title;
++++++++ELSE
++++++++++++SET+title+=+SUBSTRING(+title,+2+);++++++++++
++++++++END+IF;
++++END+WHILE;
END//
DELIMITER+;

SELECT+LFILTER('+!@#$%25%5E&*()_%2B1a++++b');|code-block|syntax|javascript|818951|此外，您可以使用正则表达式，但这需要安装MySql扩展。|818952|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I have written this UDF. However, it only trims special characters at the beginning of the string. It also converts the string to lower case. You can update this function if desired.

<pre><code>DELIMITER //

DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//

CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
 DECLARE result VARCHAR(250);
 SET result = REPLACE( title, ' ', ' ' );
 WHILE (result &lt;&gt; title) DO 
 SET title = result;
 SET result = REPLACE( title, ' ', ' ' );
 END WHILE;
 RETURN result;
END//

DROP FUNCTION IF EXISTS LFILTER//

CREATE FUNCTION LFILTER ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
 WHILE (1=1) DO
 IF( ASCII(title) BETWEEN ASCII('a') AND ASCII('z')
 OR ASCII(title) BETWEEN ASCII('A') AND ASCII('Z')
 OR ASCII(title) BETWEEN ASCII('0') AND ASCII('9')
 ) THEN
 SET title = LOWER( title );
 SET title = REPLACE(
 REPLACE(
 REPLACE(
 title,
 CHAR(10), ' '
 ),
 CHAR(13), ' '
 ) ,
 CHAR(9), ' '
 );
 SET title = DELETE_DOUBLE_SPACES( title );
 RETURN title;
 ELSE
 SET title = SUBSTRING( title, 2 ); 
 END IF;
 END WHILE;
END//
DELIMITER ;

SELECT LFILTER(' !@#$%^&amp;*()_+1a b');
</code></pre>

Also, you could use regular expressions but this requires installing a MySql extension.

blocks|key|818970|text|与其他建议相比，这可能是一个愚蠢的建议：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|818971|if(!preg_match("/%5E[a-zA-Z0-9]$/",$string)){
++++$sortedString=preg_replace("/%5E[a-zA-Z0-9]%2B$/","",$string);
}|code-block|syntax|javascript|818972|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Probably a silly suggestion compared to others:

<pre><code>if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
 $sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}
</code></pre>

blocks|key|603816|text|使用MySQL+8.0或更高版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|603817|感谢Michal.jakubeczy的回答，现在MySQL支持Regex：|603818|UPDATE+{table}+SET+{column}+=+REGEXP_REPLACE({column},+'[%5E0-9a-zA-Z+]',+'')|code-block|syntax|javascript|603819|使用MySQL+5.7或更低版本|603820|这里不支持正则表达式。我不得不创建我自己的函数alphanum，它为我剥离了字符：|603821|DROP+FUNCTION+IF+EXISTS+alphanum;+
DELIMITER+%7C+
CREATE+FUNCTION+alphanum(+str+CHAR(255)+)+RETURNS+CHAR(255)+DETERMINISTIC
BEGIN+
++DECLARE+i,+len+SMALLINT+DEFAULT+1;+
++DECLARE+ret+CHAR(255)+DEFAULT+'';+
++DECLARE+c+CHAR(1);
++IF+str+IS+NOT+NULL+THEN+
++++SET+len+=+CHAR_LENGTH(+str+);+
++++REPEAT+
++++++BEGIN+
++++++++SET+c+=+MID(+str,+i,+1+);+
++++++++IF+c+REGEXP+'[[:alnum:]]'+THEN+
++++++++++SET+ret=CONCAT(ret,c);+
++++++++END+IF;+
++++++++SET+i+=+i+%2B+1;+
++++++END;+
++++UNTIL+i+>+len+END+REPEAT;+
++ELSE
++++SET+ret='';
++END+IF;
++RETURN+ret;+
END+%7C+
DELIMITER+;+|603822|现在我可以这样做了：|603823|select+'This+works+finally!',+alphanum('This+works+finally!');|603824|我得到了：|603825|%2B---------------------%2B---------------------------------%2B
%7C+This+works+finally!+%7C+alphanum('This+works+finally!')+%7C
%2B---------------------%2B---------------------------------%2B
%7C+This+works+finally!+%7C+Thisworksfinally++++++++++++++++%7C
%2B---------------------%2B---------------------------------%2B
1+row+in+set+(0.00+sec)|603826|万岁！|603827|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|17|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|19|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

<h2>Using MySQL 8.0 or higher</h2>
Courtesy of michal.jakubeczy's answer below, replacing by Regex is now supported by MySQL:
<pre><code>UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
</code></pre>
<h2>Using MySQL 5.7 or lower</h2>
Regex isn't supported here. I had to create my own function called alphanum which stripped the chars for me:
<pre><code>DROP FUNCTION IF EXISTS alphanum; 
DELIMITER | 
CREATE FUNCTION alphanum( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN 
 DECLARE i, len SMALLINT DEFAULT 1; 
 DECLARE ret CHAR(255) DEFAULT ''; 
 DECLARE c CHAR(1);
 IF str IS NOT NULL THEN 
 SET len = CHAR_LENGTH( str ); 
 REPEAT 
 BEGIN 
 SET c = MID( str, i, 1 ); 
 IF c REGEXP '[[:alnum:]]' THEN 
 SET ret=CONCAT(ret,c); 
 END IF; 
 SET i = i + 1; 
 END; 
 UNTIL i &gt; len END REPEAT; 
 ELSE
 SET ret='';
 END IF;
 RETURN ret; 
END | 
DELIMITER ; 
</code></pre>
Now I can do:
<pre><code>select 'This works finally!', alphanum('This works finally!');
</code></pre>
and I get:
<pre><code>+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally |
+---------------------+---------------------------------+
1 row in set (0.00 sec)
</code></pre>
Hurray!

blocks|key|603859|text|我只需要在过程中获取字符串的字母字符，并做到了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|603860|SET+@source+=+"whatever+you+want";
SET+@target+=+'';
SET+@i+=+1;
SET+@len+=+LENGTH(@source);
WHILE+@i+<=+@len+DO
++++SET+@char+=+SUBSTRING(@source,+@i,+1);
++++IF+((ORD(@char)+>=+65+&&+ORD(@char)+<=+90)+%7C%7C+(ORD(@char)+>=+97+&&+ORD(@char)+<=+122))+THEN
++++++++SET+@target+=+CONCAT(@target,+@char);
++++END+IF;
++++SET+@i+=+@i+%2B+1;
END+WHILE;|code-block|syntax|javascript|603861|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I needed to get only alphabetic characters of a string in a procedure, and did:

<pre><code>SET @source = "whatever you want";
SET @target = '';
SET @i = 1;
SET @len = LENGTH(@source);
WHILE @i &lt;= @len DO
 SET @char = SUBSTRING(@source, @i, 1);
 IF ((ORD(@char) &gt;= 65 &amp;&amp; ORD(@char) &lt;= 90) || (ORD(@char) &gt;= 97 &amp;&amp; ORD(@char) &lt;= 122)) THEN
 SET @target = CONCAT(@target, @char);
 END IF;
 SET @i = @i + 1;
END WHILE;
</code></pre>

blocks|key|603905|text|我尝试了一些解决方案，但最终还是使用了replace。我的数据集是零件号，我很清楚会发生什么。但为了保持理智，我使用PHP构建了这个长查询：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|603906|$dirty+=+array('+',+'-',+'.',+',',+':',+'?',+'/',+'!',+'&',+'@');
$query+=+'part_no';
foreach+($dirty+as+$dirt)+{
++++$query+=+"replace($query,'$dirt','')";
}
echo+$query;|code-block|syntax|javascript|603907|这输出了一些我过去经常感到头疼的东西：|603908|replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,'+',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')|603909|entityMap^0|J|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

I tried a few solutions but at the end used <code>replace</code>. My data set is part numbers and I fairly know what to expect. But just for sanity, I used PHP to build the long query:

<pre><code>$dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&amp;', '@');
$query = 'part_no';
foreach ($dirty as $dirt) {
 $query = "replace($query,'$dirt','')";
}
echo $query;
</code></pre>

This outputs something I used to get a headache from:

<pre><code>replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&amp;',''),'@','')
</code></pre>

blocks|key|603985|text|我在尝试匹配数据库中略有不同的姓氏时也遇到了类似的问题。例如，有时人们输入的人名是"McDonald“，也可以是"Mc+Donald"，或者"St+John”和“St.John”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|603986|我没有尝试转换Mysql数据，而是通过创建一个函数(用PHP编写)来解决这个问题，该函数接受一个字符串并创建一个仅包含alpha的正则表达式：|603987|function+alpha_only_regex($str)+{
++++$alpha_only+=+str_split(preg_replace('/[%5EA-Z]/i',+'',+$str));
++++return+'%5E[%5Ea-zA-Z]*'.implode('[%5Ea-zA-Z]*',+$alpha_only).'[%5Ea-zA-Z]*$';
}|code-block|syntax|javascript|603988|现在，我可以使用如下查询来搜索数据库：|603989|$lastname_regex+=+alpha_only_regex($lastname);
$query+=+"SELECT+*+FROM+my_table+WHERE+lastname+REGEXP+'$lastname_regex';|603990|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I had a similar problem with trying to match last names in our database that were slightly different. For example, sometimes people entered the same person's name as "McDonald" and also as "Mc Donald", or "St John" and "St. John".

Instead of trying to convert the Mysql data, I solved the problem by creating a function (in PHP) that would take a string and create an alpha-only regular expression:

<pre><code>function alpha_only_regex($str) {
 $alpha_only = str_split(preg_replace('/[^A-Z]/i', '', $str));
 return '^[^a-zA-Z]*'.implode('[^a-zA-Z]*', $alpha_only).'[^a-zA-Z]*$';
}
</code></pre>

Now I can search the database with a query like this:

<pre><code>$lastname_regex = alpha_only_regex($lastname);
$query = "SELECT * FROM my_table WHERE lastname REGEXP '$lastname_regex';
</code></pre>

blocks|key|604053|text|基于the+answer+by+Ryan+Shillington进行修改，以处理长度超过255个字符的字符串，并保留原始字符串中的空格。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|604054|仅供参考，最后是lower(str)。|style|CODE|604055|我用这个来比较字符串：|604056|DROP+FUNCTION+IF+EXISTS+spacealphanum;
DELIMITER+$$
CREATE+FUNCTION+`spacealphanum`(+str+TEXT+)+RETURNS+TEXT+CHARSET+utf8
BEGIN+
++DECLARE+i,+len+SMALLINT+DEFAULT+1;+
++DECLARE+ret+TEXT+DEFAULT+'';+
++DECLARE+c+CHAR(1);+
++SET+len+=+CHAR_LENGTH(+str+);+
++REPEAT+
++++BEGIN+
++++++SET+c+=+MID(+str,+i,+1+);+
++++++IF+c+REGEXP+'[[:alnum:]]'+THEN+
++++++++SET+ret=CONCAT(ret,c);+
++++++ELSEIF++c+=+'+'+THEN
++++++++++SET+ret=CONCAT(ret,"+");
++++++END+IF;+
++++++SET+i+=+i+%2B+1;+
++++END;+
++UNTIL+i+>+len+END+REPEAT;+
++SET+ret+=+lower(ret);
++RETURN+ret;+
++END+$$
++DELIMITER+;|code-block|syntax|javascript|604057|entityMap|0|LINK|mutability|MUTABLE|url|/a/22903586^0|2|U|0|0|8|A|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@$A|11|B|12|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|14|8|@]|9|@]|C|$M|N]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Based on <a href="/a/22903586">the answer by Ryan Shillington</a>, modified to work with strings longer than 255 characters and preserving spaces from the original string. 

FYI there is <code>lower(str)</code> in the end.

I used this to compare strings:

<pre><code>DROP FUNCTION IF EXISTS spacealphanum;
DELIMITER $$
CREATE FUNCTION `spacealphanum`( str TEXT ) RETURNS TEXT CHARSET utf8
BEGIN 
 DECLARE i, len SMALLINT DEFAULT 1; 
 DECLARE ret TEXT DEFAULT ''; 
 DECLARE c CHAR(1); 
 SET len = CHAR_LENGTH( str ); 
 REPEAT 
 BEGIN 
 SET c = MID( str, i, 1 ); 
 IF c REGEXP '[[:alnum:]]' THEN 
 SET ret=CONCAT(ret,c); 
 ELSEIF c = ' ' THEN
 SET ret=CONCAT(ret," ");
 END IF; 
 SET i = i + 1; 
 END; 
 UNTIL i &gt; len END REPEAT; 
 SET ret = lower(ret);
 RETURN ret; 
 END $$
 DELIMITER ;
</code></pre>

blocks|key|604102|text|拉丁和西里尔字符的直接和争斗的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|604103|DELIMITER+//

CREATE+FUNCTION+`remove_non_numeric_and_letters`(input+TEXT)
++RETURNS+TEXT
++BEGIN
++++DECLARE+output+TEXT+DEFAULT+'';
++++DECLARE+iterator+INT+DEFAULT+1;
++++WHILE+iterator+<+(LENGTH(input)+%2B+1)+DO
++++++IF+SUBSTRING(input,+iterator,+1)+IN
+++++++++('0',+'1',+'2',+'3',+'4',+'5',+'6',+'7',+'8',+'9',+'A',+'B',+'C',+'D',+'E',+'F',+'G',+'H',+'I',+'J',+'K',+'L',+'M',+'N',+'O',+'P',+'Q',+'R',+'S',+'T',+'U',+'V',+'W',+'X',+'Y',+'Z',+'a',+'b',+'c',+'d',+'e',+'f',+'g',+'h',+'i',+'j',+'k',+'l',+'m',+'n',+'o',+'p',+'q',+'r',+'s',+'t',+'u',+'v',+'w',+'x',+'y',+'z',+'А',+'Б',+'В',+'Г',+'Д',+'Е',+'Ж',+'З',+'И',+'Й',+'К',+'Л',+'М',+'Н',+'О',+'П',+'Р',+'С',+'Т',+'У',+'Ф',+'Х',+'Ц',+'Ч',+'Ш',+'Щ',+'Ъ',+'Ы',+'Ь',+'Э',+'Ю',+'Я',+'а',+'б',+'в',+'г',+'д',+'е',+'ж',+'з',+'и',+'й',+'к',+'л',+'м',+'н',+'о',+'п',+'р',+'с',+'т',+'у',+'ф',+'х',+'ц',+'ч',+'ш',+'щ',+'ъ',+'ы',+'ь',+'э',+'ю',+'я')
++++++THEN
++++++++SET+output+=+CONCAT(output,+SUBSTRING(input,+iterator,+1));
++++++END+IF;
++++++SET+iterator+=+iterator+%2B+1;
++++END+WHILE;
++++RETURN+output;
++END+//

DELIMITER+;|code-block|syntax|javascript|604104|用法：|604105|--+outputs+"hello12356"
SELECT+remove_non_numeric_and_letters('hello+-+12356-привет+""]')|604106|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Straight and battletested solution for latin and cyrillic characters:

<pre><code>DELIMITER //

CREATE FUNCTION `remove_non_numeric_and_letters`(input TEXT)
 RETURNS TEXT
 BEGIN
 DECLARE output TEXT DEFAULT '';
 DECLARE iterator INT DEFAULT 1;
 WHILE iterator &lt; (LENGTH(input) + 1) DO
 IF SUBSTRING(input, iterator, 1) IN
 ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я', 'а', 'б', 'в', 'г', 'д', 'е', 'ж', 'з', 'и', 'й', 'к', 'л', 'м', 'н', 'о', 'п', 'р', 'с', 'т', 'у', 'ф', 'х', 'ц', 'ч', 'ш', 'щ', 'ъ', 'ы', 'ь', 'э', 'ю', 'я')
 THEN
 SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
 END IF;
 SET iterator = iterator + 1;
 END WHILE;
 RETURN output;
 END //

DELIMITER ;
</code></pre>

Usage:

<pre><code>-- outputs "hello12356"
SELECT remove_non_numeric_and_letters('hello - 12356-привет ""]')
</code></pre>

blocks|key|604136|text|如果你正在使用php，那么....|type|unstyled|depth|inlineStyleRanges|entityRanges|data|604137|try{
$con+=+new+PDO+("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException+$e){
echo+"error".$e-getMessage();+++
}

$select+=+$con->prepare("SELECT+*+FROM+table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();

while($data=$select->fetch()){+

$id+=+$data['id'];
$column+=+$data['column'];
$column+=+preg_replace("/[%5Ea-zA-Z0-9]%2B/",+"+",+$column);+//remove+all+special+characters

$update+=+$con->prepare("UPDATE+table+SET+column=:column+WHERE+id='$id'");
$update->bindParam(':column',+$column+);
$update->execute();

//+echo+$column." ";
}+|code-block|syntax|javascript|604138|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

if you are using php then.... 

<pre><code>try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage(); 
}

$select = $con-&gt;prepare("SELECT * FROM table");
$select-&gt;setFetchMode(PDO::FETCH_ASSOC);
$select-&gt;execute();

while($data=$select-&gt;fetch()){ 

$id = $data['id'];
$column = $data['column'];
$column = preg_replace("/[^a-zA-Z0-9]+/", " ", $column); //remove all special characters

$update = $con-&gt;prepare("UPDATE table SET column=:column WHERE id='$id'");
$update-&gt;bindParam(':column', $column );
$update-&gt;execute();

// echo $column."&lt;br&gt;";
} 
</code></pre>

blocks|key|819348|text|alphanum函数(self+answered)有一个bug，但我不知道为什么。对于文本"cas+synt+ls+75W140+1L“返回"cassyntls75W1401"，末尾的"L”缺少一些how。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|819349|现在我使用|819350|delimiter+//
DROP+FUNCTION+IF+EXISTS+alphanum+//
CREATE+FUNCTION+alphanum(prm_strInput+varchar(255))
RETURNS+VARCHAR(255)
DETERMINISTIC
BEGIN
++DECLARE+i+INT+DEFAULT+1;
++DECLARE+v_char+VARCHAR(1);
++DECLARE+v_parseStr+VARCHAR(255)+DEFAULT+'+';
WHILE+(i+<=+LENGTH(prm_strInput)+)++DO
++SET+v_char+=+SUBSTR(prm_strInput,i,1);
++IF+v_char+REGEXP++'%5E[A-Za-z0-9]%2B$'+THEN+
++++++++SET+v_parseStr+=+CONCAT(v_parseStr,v_char);++
++END+IF;
++SET+i+=+i+%2B+1;
END+WHILE;
RETURN+trim(v_parseStr);
END
//|code-block|syntax|javascript|819351|(可在谷歌上找到)|819352|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

the alphanum function (self answered) have a bug, but I don't know why.
For text "cas synt ls 75W140 1L" return "cassyntls75W1401", "L" from the end is missing some how.

Now I use 

<pre><code>delimiter //
DROP FUNCTION IF EXISTS alphanum //
CREATE FUNCTION alphanum(prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
 DECLARE i INT DEFAULT 1;
 DECLARE v_char VARCHAR(1);
 DECLARE v_parseStr VARCHAR(255) DEFAULT ' ';
WHILE (i &lt;= LENGTH(prm_strInput) ) DO
 SET v_char = SUBSTR(prm_strInput,i,1);
 IF v_char REGEXP '^[A-Za-z0-9]+$' THEN 
 SET v_parseStr = CONCAT(v_parseStr,v_char); 
 END IF;
 SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//
</code></pre>

(found on google)

blocks|key|819411|text|从MySQL+8.0开始，您可以使用正则表达式从字符串中删除非字母数字字符。有一个REGEXP_REPLACE方法|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|819412|以下是删除非字母数字字符的代码：|819413|UPDATE+{table}+SET+{column}+=+REGEXP_REPLACE({column},+'[%5E0-9a-zA-Z+]',+'')|code-block|syntax|javascript|819414|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace^0|15|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Since MySQL 8.0 you can use regular expression to remove non alphanumeric characters from a string. There is method <a href="https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace" rel="noreferrer">REGEXP_REPLACE</a>

Here is the code to remove non-alphanumeric characters:

<pre><code>UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
</code></pre>

I'm working on a routine that compares strings, but for better efficiency I need to remove all characters that are not letters or numbers.

I'm using multiple <code>REPLACE</code> functions now, but maybe there is a faster and nicer solution ?

How to remove all non-alpha numeric characters from a string in MySQL?

我正在编写一个比较字符串的例程，但为了提高效率，我需要删除除字母或数字之外的所有字符。我现在使用了多个REPLACE函数，但也许有更快更好的解决方案？

问如何在MySQL中删除字符串中的所有非字母数字字符？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中删除字符串中的所有非字母数字字符？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中删除字符串中的所有非字母数字字符？
EN