Find/Cut string from first integer in MySQL

Question

I'm stuck with a problem, in my database i've values like this:

GSB45-B, GSBD60-01, etc.

Now i need to get the values GSB / GSBD out of these strings. So in fact i need te first character from te start to the first integer.

Hopefully someone can help me, thanks from now.

No, i need GSB, GSBD. (the chars till the first integrer). Thanks. — Ronn0
– Ronn0, Commented Jun 21, 2012 at 8:45
@Ronn0 : Could you please check my answer and let me know if that is fine with you? — Fahim Parkar
– Fahim Parkar, Commented Jun 22, 2012 at 12:02

Zane Bien · Accepted Answer · 2012-06-21 09:03:22Z

2

It's a bit of a clunky solution, but you can try:

SELECT 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(column, '-', 1), '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') AS extracted
FROM
    yourtbl

answered Jun 21, 2012 at 9:03

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zane Bien Over a year ago

Great! I'm glad that it works and you only need to run it once! I'm sure many others will laugh hysterically at first sight of all those REPLACEs, but it's really the only way you can do it AFAIK! =)

sel · Accepted Answer · 2012-06-21 09:36:22Z

1

DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);
  SET len = CHAR_LENGTH( str );
  label1: LOOP
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=SUBSTRING( str, 1, i-1);

        LEAVE label1;
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  END LOOP label1;
  RETURN ret;
END |
DELIMITER ;

select digits('GSB45-B'),digits('GSBD60-01') ,digits('KKGSBD60-01') ;

answered Jun 21, 2012 at 9:36

sel

4,9421 gold badge18 silver badges23 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:34:26Z

1

How about this??

SELECT 
  myWord, 
  SUBSTR(myWord,1,LEAST (
    if (Locate('0',myWord) >0,Locate('0',myWord),999),
    if (Locate('1',myWord) >0,Locate('1',myWord),999),
    if (Locate('2',myWord) >0,Locate('2',myWord),999),
    if (Locate('3',myWord) >0,Locate('3',myWord),999),
    if (Locate('4',myWord) >0,Locate('4',myWord),999),
    if (Locate('5',myWord) >0,Locate('5',myWord),999),
    if (Locate('6',myWord) >0,Locate('6',myWord),999),
    if (Locate('7',myWord) >0,Locate('7',myWord),999),
    if (Locate('8',myWord) >0,Locate('8',myWord),999),
    if (Locate('9',myWord) >0,Locate('9',myWord),999)
  )-1) as NewString
FROM myTable;

Demo

Also read, Using alias name in another column. This is same question as yours.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jun 21, 2012 at 9:05

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

4 Comments

Zane Bien Over a year ago

Unfortunately, REGEXP only returns 0 or 1 based on if it matched the pattern or not, not the extracted match. As a result, your LOCATE() will only be able to return 0 or -1, not the index of the first number.

Zane Bien Over a year ago

No, because the value passed to LOCATE() will only be a boolean 0 or 1, not the index of the first occurrence of a number as you would intuitively expect. It's a shame that MySQL doesn't support extracting strings based on patterns...

Ronn0 Over a year ago

They both give:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEXP '[0-9]')-1) AS 'the_string' FROM products LIMIT 0, 30' at line 1.

Jake Feasel Over a year ago

@FahimParkar SQL Fiddle is back now, btw. Sorry about the outage!

Olivier Coilland · Accepted Answer · 2012-06-21 09:03:15Z

0

MySQL can't do REGEXP extraction, and that's a really sad thing :(

Assuming that the pattern of your values is [A-Z]*[0-9]{2}-.* you can use this ugly thing:

SELECT
    LEFT(
        SUBSTRING_INDEX("GSB45-B", "-", 1),
        LENGTH(SUBSTRING_INDEX("GSB45-B", "-", 1)) - 2
    ) as CODE;

But I would recommand doing your parsing on the application side :)

answered Jun 21, 2012 at 9:03

Olivier Coilland

3,11618 silver badges20 bronze badges

3 Comments

Fahim Parkar Over a year ago

what if string is GSB4543-B? Isn't your code will change to - 4

Ronn0 Over a year ago

This almost worked, but i did post a bad example. There are also codes with 3 digits GSBD603-03. Thanks for the help!

Olivier Coilland Over a year ago

Indeed Fahim Parkar, that's why I made the assumption on the pattern :)

Collectives™ on Stack Overflow

Find/Cut string from first integer in MySQL

4 Answers 4

1 Comment

Comments

Demo

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related