Choosing substring depending on character or numeric

Question

I got a column with either of two ways

XXX ZZZ 1.1
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.

Which dbms are you using? (The above code is product specific.) — jarlh, Aug 23 '21 at 12:15
You need to use a space as the delimiter, your current delimiter is an empty string. I would reverse the value first, seeing you are looking for the last one, after reversing you match on the first space then reverse the result. — Chris Schaller, Aug 23 '21 at 12:24
also your function doesn't have a verb as the name, instead you've called it `table` which really doesn't explain the intent at all. Please give your function a meaningful name and update your post to be tagged with mysql — Chris Schaller, Aug 23 '21 at 12:29
@ChrisSchaller Thanks for the idea. I really appreciate. I also can appreciate you trying people to tag better their questions. But talking about the function name is overstepping. As u can understand I am not giving every single detail in a forum. Anyway, thanks for the help man. — fqv572, Aug 23 '21 at 12:47
naming a function `table` is going to confuse the next person who can't tell the difference between tables or functions or views or stored procedures, so I'm sorry for being pedantic but if you don't change it I will, because as it stands this code example is too ambiguous to be meaningful for the rest of the community. — Chris Schaller, Aug 23 '21 at 12:50
Does this answer your question? [Last index of a given substring in MySQL](https://stackoverflow.com/questions/12775352/last-index-of-a-given-substring-in-mysql) — Chris Schaller, Aug 23 '21 at 12:54

score 1 · Answer 1 · answered Aug 23 '21 at 12:52

1

The solution would be some like

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

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

answered Aug 23 '21 at 12:52

fqv572

39
5

score 0 · Accepted Answer · answered Aug 23 '21 at 12:59

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

Choosing substring depending on character or numeric

2 Answers2