CODEWITHSUNDEEP
mysqlregexselectsubstring
Led
Led

Reputation: 517

MYSQL substring with delimiters

Assuming I have a table that returns set of values,

+--------------------------+
|         MyColumn         |
+--------------------------+
| some string 929190. here |
| basic 092019. test       |
| lorem i 092988.          |
| 029109magic. lorem ipsum |
+--------------------------+

what i need to do is select from that table with the following rules 1)if the number has space in front and ends with a "." get the number and remove the remaining values 2)get the text after "." and place it to a new column

+--------------------------+-------------+
|         MyColumn         |   string    |
+--------------------------+-------------+
| 929190                   | here        |
| 092019                   | test        |
| 092988                   |             |
| 029109magic              | lorem ipsum |
+--------------------------+-------------+

I managed to do one, but its only good for a specific case.

select (CASE
    WHEN MyColumn REGEXP '([0-9]{7}.$)' THEN SUBSTR(MyColumn, 8, 6)
    ) from TableTest

any other workaround or shortcut? I might end up declaring all possible scenarios?

Upvotes: 0

Views: 470

Answers (1)

Rick James
Rick James

Reputation: 142228

Item 2:

UPDATE tbl
     SET string = SUBSTRING_INDEX(mycol, '.', -1),
         mycol  = SUBSTRING_INDEX(mycol, '.',  1) ;

Item 1 cannot be done in MySQL, but probably can be done with MariaDB and its REGEXP_REPLACE or REGEXP_SUBSTR.

Upvotes: 2

Related Questions