How do I to extract a converted value from my db?
SELECT name FROM clients LIMIT 1;
It will list: "13's Automotors"
But I want to show "13SAUTOMOTORS", listing only letters and numbers without spaces.
Server type: Percona Server Server version: 5.6.40-84.0-log - Percona Server (GPL), Release 84.0, Revision 47234b3 Protocol version: 10
Newer versions (MySQL 8 and MariaDB 10+) support the REGEXP_REPLACE() function.
SELECT regexp_replace(name, '[^\\d\\w]', '') as converted_name
FROM clients
will replace all non-digit and non-word characters with an empty string.
If you need the result in uppercase, use UPPER()
SELECT upper(regexp_replace(name, '[^\\d\\w]', '')) as converted_name
FROM clients
If your version doesn't support REGEXP_REPLACE(), consider to do the conversion in your application language. Since you've tagged your question with mysqli, I assume that you are using PHP. Then you can use pred_replace() and strtoupper():
$row['converted_name'] = preg_replace('/[^\\d\\w]/', '', $row['name']);
$row['converted_name'] = strtoupper($row['converted_name']);
In older versions of MySQL you can use a bunch of nested replace operations for this.
SELECT UPPER(REPLACE(REPLACE('3''s Automotors', ' ',''),'''','')) val
It's a bit nasty because you need a nested REPLACE for each possible character you want to remove, but it works.
Notice you must represent single-quote characters ' in string constants in MySQL queries by doubling them. So, '''' represents the single character ' as a string constant.
