Match two mysql cols on alpha chars (ignoring numbers in same field)

Question

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

So something like

SELECT col1, col2, **alpha_chars_only(col3)** FROM table 

I am not looking to update only select.

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.

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.

Any help very much wel come

Answer 1

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 ;