extract substring from mysql column using regex

Question

My regex is (pnr|(P|p) _.:,!"'-/$ _.:,!"'-/$)+[ _.:,!"'-/$]+[0-9]{3}[ _.:,!"'-/$]+[0-9]{7}

It is extracting pnr number from column .

sample text : 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding pnr:986-097832

94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: pnr:986-097832 text/plain; charset=UTF-8\r\nContent-Transfer-Encoding

pnr:986-097832 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding

I have to create a mysql query which will extract only the pnr number

Answer 1

For Oracle we can do something like below -

    SQL> create table test2( id varchar2(2000));

    Table created.

    SQL> insert into test2 values ('pnr:986-097831 94eb2c0cb17ef354bb052c57f40c\r\nC
    ontent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding');

    1 row created.

    SQL> insert into test2 values('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: pnr
    :986-097832 text/plain; charset=UTF-8\r\nContent-Transfer-Encoding')
      2  ;

    1 row created.

    SQL> insert into test2 values('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: tex
    t/plain; charset=UTF-8\r\nContent-Transfer-Encoding pnr:986-097833');

    1 row created.

    SQL> select regexp_substr( id, '(P|p)(N|n)(R|r):[0-9]*\-[0-9]*' ) PNR FROM  test2;

    PNR
----------------

pnr:986-097831
pnr:986-097832
pnr:986-097833

    SQL>

`

Answer 2

SELECT REGEXP_SUBSTR(column, 'pnr:[0-9\-]{10}')
FROM table

Answer 3

Try SUBSTRING_INDEX

SELECT 
SUBSTRING_INDEX(
SUBSTRING_INDEX('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding pnr:986-097832', 'pnr:', -1), ' ',1);

SELECT 
SUBSTRING_INDEX(
SUBSTRING_INDEX('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: pnr:986-097832 text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1);

SELECT 
SUBSTRING_INDEX(
SUBSTRING_INDEX('pnr:986-097832 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1);

sample

mysql> SELECT
    -> SUBSTRING_INDEX(
    -> SUBSTRING_INDEX('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: pnr:986-097832 text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(
SUBSTRING_INDEX('94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: pnr:986-097832 text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 986-097832                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>
mysql> SELECT
    -> SUBSTRING_INDEX(
    -> SUBSTRING_INDEX('pnr:986-097832 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SUBSTRING_INDEX(
SUBSTRING_INDEX('pnr:986-097832 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding', 'pnr:', -1), ' ',1) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 986-097832                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>