2

What i get value in php

    $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123');

// abc123

Is it possible to use preg_replace in my mysql query?

I want to something like this. want to compare like following

SELECT `id`,`user_id`,`subject` FROM `mail` where preg_replace('/[^A-Za-z0-9]/', '',`subject`)=$subject

I know what i write above is not available. Still i want help. I didn't get proper answer.

I tried with function

CREATE DEFINER=`root`@`localhost` FUNCTION `removeExtraChar`(`str` VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    NO SQL
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) 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); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END

and call it like

My table data

id   user_id   subject
----------------------
1      1        abc-123
2      1        xyz-234
3      2        new-post

SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=$subject

but this goes in infinite loop. Nothing executes Note: I don't want to use extra mysql plugin.

  • Upgrade to MariaDB and use its REGEXP_REPLACE. – Rick James Apr 22 '16 at 23:09
0

For similar purposes, you can also rely on a series and GROUP_CONCAT:

SET @str:='abc - 123';
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c FROM myseries
    WHERE id BETWEEN 1 AND LENGTH(@str)
) a
WHERE c REGEXP '[[:alnum:]]';

For this you could keep a myseries table for general use, it can be quickly created with 10,000 rows like this:

CREATE TABLE myseries (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO myseries
SELECT NULL FROM information_schema.columns, information_schema.columns c2 LIMIT 10000;

Or create one on the fly from any table large enough:

SET @str:='abc - 123';
SET @len:=LENGTH(@str);
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c
    FROM (
        SELECT @num:=@num+1 AS id
        FROM (SELECT @num:=0) num, yourtable
        WHERE @num<=@len
    ) myseries
) a
WHERE c REGEXP '[[:alnum:]]';
0

I think that is your query the problem here. If you used the function in the field subject if isnt indexed, this could take hours depending the amount of rows. You should call it in this way:

SELECT 
    `id`, `user_id`, `subject`
FROM `mail`
WHERE `subject` = removeExtraChar('abc - 123');

I tested your function and it worked:

mysql> SELECT removeExtraChar('abc - 123');
+------------------------------+
| removeExtraChar('abc - 123') |
+------------------------------+
| abc123                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> 

EDIT 1

I've simulate your data example:

mysql> SELECT * FROM  `mail`;
+----+---------+----------+
| id | user_id | subject  |
+----+---------+----------+
|  1 |       1 | abc-123  |
|  2 |       1 | xyz-234  |
|  3 |       2 | new-post |
+----+---------+----------+
3 rows in set (0.00 sec)

The only complication with this is that it will not use any index on subject field because your use this function on it.

mysql> EXPLAIN SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=removeExtraChar('abc - 123');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | mail  | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

The query:

mysql> SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=removeExtraChar('abc - 123');
+----+---------+---------+
| id | user_id | subject |
+----+---------+---------+
|  1 |       1 | abc-123 |
+----+---------+---------+
1 row in set (0.00 sec)

mysql> 
  • my subject field has value with special character. and in query i want to remove all that extra character from values and then compare it with my given value – Jazz Apr 20 '16 at 5:25
  • Add some data example for your table mail so I can change the query. – oNare Apr 20 '16 at 13:09
  • i have updated my question – Jazz Apr 21 '16 at 10:55
  • Edited, try now. – oNare Apr 21 '16 at 14:43
  • thanks oNare.. but its take too much time. its loading. can't get any result – Jazz Apr 23 '16 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.