CODEWITHSUNDEEP
mysqlsqlfunction
fqv572
fqv572

Reputation: 39

Choosing substring depending on character or numeric

I got a column with either of two ways

  1. XXX ZZZ 1.1

  2. XXX 1.1

So the point is I just to get de 1.1, no matter the way data is coming (1 or 2).

I got the next function in SQL to split data when it finds a blank just like

FUNCTION `ddbb`.`function`(

    `x` VARCHAR(255),

    `delim` VARCHAR(12),

    `pos` INT

) RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    READS SQL DATA
BEGIN

RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),

       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),

       delim, '');

END

How do I continue the function to get just the number wherever is it located?

My other option is keeping function as it is, and getting the right position in the query but I've also got problems with it.

Upvotes: 1

Views: 63

Answers (2)

nbk
nbk

Reputation: 49375

Mysql has SUBSTRING_INDEX which you can use

CREATE tABLe A (ab varchar(20))

INSERT INTO A VALUES
    ('XXX ZZZ 1.1'),
   ('XXX 1.1')

SELECT SUBSTRING_INDEX(ab,' ', -1) FROM A

| SUBSTRING_INDEX(ab,' ', -1) |
| :-------------------------- |
| 1.1                         |
| 1.1                         |

db<>fiddle here

Upvotes: 0

fqv572
fqv572

Reputation: 39

The solution would be some like

SELECT SUBSTRING_INDEX("first_middle_last", '_', -1);

Taken from https://stackoverflow.com/a/18422739/11298427

Upvotes: 1

Related Questions