3

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.

Ronn0
  • 2,249
  • 2
  • 21
  • 36

4 Answers4

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
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • 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 `REPLACE`s, but it's really the only way you can do it AFAIK! =) – Zane Bien Jun 21 '12 at 09:16
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.

Community
  • 1
  • 1
Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
  • 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 Jun 21 '12 at 09:09
  • 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... – Zane Bien Jun 21 '12 at 09:13
  • 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. – Ronn0 Jun 21 '12 at 09:16
  • @FahimParkar SQL Fiddle is back now, btw. Sorry about the outage! – Jake Feasel Jun 21 '12 at 15:03
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') ;
sel
  • 4,982
  • 1
  • 16
  • 22
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 :)

Olivier Coilland
  • 3,088
  • 16
  • 20