CODEWITHSUNDEEP
mysqlregexsubstring
Daniel Jørgensen
Daniel Jørgensen

Reputation: 1202

MySQL Select only part of column

How can i select only a part of a column ? I know I can use substring, but i need to select a string part of a column. As an example: Column can contain the following:

DIRL100

I need to select only the DIRL part in one column, and the 100 part as another.

I could do it with this specific column like so:

SELECT SUBSTRING(column, 5) AS part1, SUBSTRING(column, 1, 4) AS part2 ....

But i cannot be sure that its always 4 letters (DIRL) before it gets numeric .. Can i somehow use REGEXP or something to extract only the numeric part and the letter part in each column ?

In other words.. Can i split a column by where the letters end. It could as an example contain DIRL100 or AB100200 which should be split into two columns each containing the letters from the column (DIRL or AB) and the digits from the column (100 or 100200) ?

Upvotes: 1

Views: 2232

Answers (2)

Ernest Han
Ernest Han

Reputation: 434

Unfortunately, the regexp functions in MySQL is limited. You have to write a custom function to help you. 

DROP FUNCTION IF EXISTS get_index;

DELIMITER $$
CREATE FUNCTION get_index(targetString VARCHAR(255)) RETURNS INTEGER
BEGIN

    DECLARE i INTEGER;
    DECLARE min_index INTEGER;
    DECLARE current_index INTEGER;
    SET i = 0;
    SET min_index = NULL;

    loopNumbers: WHILE (i <= 9) DO
        SET current_index = LOCATE(i, targetString);
        IF current_index > 0 THEN
            IF min_index IS NULL OR current_index < min_index THEN
              SET min_index = current_index;
            END IF;
        END IF;

        SET i = i + 1;

    END WHILE; 

    RETURN(min_index);

END
$$
DELIMITER ;

What this function does is to obtain the first position of the numbers. Then using this function, you can modify your query to:

SELECT SUBSTRING(column_name FROM 1 FOR get_index(column_name)-1) AS first, SUBSTRING(column_name FROM get_index(column_name)) AS second 
FROM table_name
WHERE column_name REGEXP '^[a-Z]+[0-9]+$'

The additional WHERE condition is optional to ensure that only data in the correct format is selected.

Upvotes: 1

Francois
Francois

Reputation: 157

Try this request:

SELECT LEFT(column, patindex('%[0-9]%', column)-1) AS Part1
     , RIGHT(column, LEN(column) - patindex('%[0-9]%', column)+1) AS Part2

Upvotes: 1

Related Questions