get the maximum number that extracted from a string using mysql

Question

I have a table called Elements

id    reference
101   AES/JN/2001
102   AMES/JN/2001
103   AES/JN/2002
104   AES/JN/2003
105   AMES/JN/2002

I want to get the maximum number from the string. If my search key word is AMES/JN I should get 2002. And If my key word is AES/JN then output should be 2003

I have tried the following code:

select max(convert(substring_index(reference,'/', -1), unsigned)) as max
        FROM Elements  WHERE reference like 'AES/JN/'

score 2 · Answer 1 · answered Jun 12 '17 at 10:56

I almost agree with Shyam except for that horribly convoluted function.

I recommend this query:

SELECT SUBSTRING_INDEX(reference,'/',-1) as `max`
FROM `Elements`
WHERE reference LIKE 'AES/JN/%'
ORDER BY reference DESC
LIMIT 1

This will output a single row with with 2003 as the value in the max column.

The reason I like this method is because CONVERT() is omitted/unnecessary.

I've compared my query against Xenofexs' on my server and mine is only .0001 seconds faster -- but this is only running on the 5 rows that the OP posted. As the database volume increases, I am confident that my query's performance lead will increase.

Even if you don't care about the micro-optimization, I think this query is easier to read/comprehend because it doesn't have a function inside a function inside a function.

In fact, I believe this next query may outperform my above query:

SELECT SUBSTRING_INDEX(reference,'/',-1) as `max`
FROM `Elements`
WHERE LOCATE('AES/JN/',reference)
ORDER BY reference DESC
LIMIT 1

Because LOCATE() will be checking the leading characters from the reference column, and the targeted substring will not occur later in the string, LOCATE() has been benchmarked to outperform LIKE.

Additional reading:
MySQL LIKE vs LOCATE

For the record, here is the table that I used:

CREATE TABLE `Elements` (
  `id` int(10) NOT NULL,
  `reference` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `Elements` (`id`, `reference`) VALUES
(101, 'AES/JN/2001'),
(102, 'AMES/JN/2001'),
(103, 'AES/JN/2002'),
(104, 'AES/JN/2003'),
(105, 'AMES/JN/2002');

ALTER TABLE `Elements`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `Elements`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=106;

You should use the `LIKE` solution, and add an index on the `reference` column. The index will help the `LIKE` perform faster, and it will also help the `ORDER BY`. The LOCATE() solution cannot use an index and it will force a table-scan. Use EXPLAIN to confirm this. — Bill Karwin, Jun 12 '17 at 15:04
P.S. [Don't use MyISAM](https://stackoverflow.com/a/17706717/20860). :-) — Bill Karwin, Jun 12 '17 at 15:05

score 1 · Accepted Answer · answered Jun 12 '17 at 08:19

Please check how "LIKE" work's. You can use % as joker

Just change your query and add % character. And it's work

    SELECT
    max(
        CONVERT (
            substring_index(reference, '/', - 1),
            UNSIGNED
        )
    ) AS max
FROM
    reference
WHERE
    reference LIKE 'AES/JN/%'

Please note : LIKE 'AES/JN/%'

Sagar Gangwal · Answer 3 · 2017-06-12T08:45:40.960

0

SELECT MAX(Z.COUNT),reference FROM
(
  SELECT reference,CAST(SUBSTRING_INDEX(reference, '/', -1) AS DECIMAL) count 
  FROM Elements where reference like 'AES/JN/%'
)Z

Try above code.

Hope this helps.

edited Jun 12 '17 at 08:45

answered Jun 12 '17 at 08:02

Sagar Gangwal

7,544
3
24
38

Do comment ,if you are down voting some one..!! – Sagar Gangwal Jun 12 '17 at 08:46
I didn't downvote this one, but whom ever did probably did so because you are using a subquery when the desired result can be achieved without one. – mickmackusa Jun 12 '17 at 09:42
@mickmackusa Yes got it. – Sagar Gangwal Jun 12 '17 at 09:45

score 0 · Answer 4 · answered Jun 12 '17 at 08:25

Please find below solution.

Query:

select id,reference,digits(reference) as num_values from asasas where reference like 'AMES/JN%' order by num_values DESC limit 1

You need to create one function in mysql

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`digits`$$

CREATE FUNCTION `digits`( str CHAR(32) ) RETURNS char(32) CHARSET latin1
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);
  IF str IS NULL
  THEN 
    RETURN "";
  END IF;
  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END$$

DELIMITER ;

Let me know if it not works for you

get the maximum number that extracted from a string using mysql

4 Answers4