在 alpha 字符上匹配两个 mysql cols(忽略同一字段中的数字) [英] Match two mysql cols on alpha chars (ignoring numbers in same field)
问题描述
我想知道您是否知道我可以过滤 mysql 查询以仅显示特定字段中的alpha"字符的方法
I was wondering if you know of a way I could filter a mysql query to only show the ‘alpha’ characters from a specific field
所以就像
SELECT col1, col2, **alpha_chars_only(col3)** FROM table
我不想只更新选择.
我一直在研究一些正则表达式,但运气不佳,大部分结果都是搜索只包含alpha"字符的字段.
I have been looking at some regex but without much luck most of what turned up was searching for fields that only contain ‘alpha’ chars.
在一个被淡化的背景下......我有包含 abc 的 col1 和包含 abc123 的 col 2,我只想在 alpha 字符上匹配它们.可以有任意数量的字母或数字.
In a much watered down context... I have col1 which contains abc and col two contains abc123 and I want to match them on alpha chars only. There can be any number of letters or numbers.
非常欢迎任何帮助
推荐答案
您可能需要为此编写自定义函数.如果你想在 MySQL 中这样做,你可以创建一个这样的存储函数:
You probably need to write a custom function for this. If you want to do it in MySQL, you could create a stored function like this:
DELIMITER $$
drop function if exists alpha_chars_only $$
create function alpha_chars_only (p_string text) returns text
begin
declare v_return_val text default '';
declare v_iter int unsigned default 1;
declare v_length int unsigned default 0;
declare v_char char(1) default null;
set v_length = char_length(p_string);
while (v_iter <= v_length)
do
set v_char = substring(p_string,v_iter,1);
if (v_char REGEXP '[a-z]')
then
set v_return_val = concat(v_return_val,v_char);
end if;
set v_iter = v_iter + 1;
end while;
return v_return_val;
end $$
DELIMITER ;
这篇关于在 alpha 字符上匹配两个 mysql cols(忽略同一字段中的数字)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!