extract substring from mysql column using regex

Question

My regex is

(pnr|(P|p)[ _.:,!"'-/$](N|n)[ _.:,!"'-/$](R|r))+[ _.:,!"'-/$]+[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

Blue · Accepted Answer · 2018-10-23 01:48:45Z

23

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

edited Oct 23, 2018 at 1:48

Blue

23k7 gold badges69 silver badges102 bronze badges

answered Oct 22, 2018 at 22:22

benito

2392 silver badges2 bronze badges

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

3 Comments

Blue Over a year ago

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.

Roshan Upreti Over a year ago

Neat and simple.

powtac Over a year ago

REGEXP_SUBSTR only for MySQL >= 8.0 !

Bernd Buffen · Accepted Answer · 2017-02-28 05:50:35Z

10

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>

answered Feb 28, 2017 at 5:50

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

5 Comments

Sunil Harak Over a year ago

i'm trying to use substring_index but i have to use it with regex as pnr can be p:n:r or PNR or p.n.r : 123-3123212

Bernd Buffen Over a year ago

@Sunil Harak - you can try to normalize "pnr" like this and put this in my query SELECT REPLACE( REPLACE( REPLACE('pnr:986-097832 94eb2c0cb17ef354bb052c57f40c\r\nContent-Type: text/plain; charset=UTF-8\r\nContent-Transfer-Encoding' ,'p:n:r','PNR') ,'p.n.r','PNR') ,'pnr','PNR');

Bernd Buffen Over a year ago

@Sunil Harak - or if you using MariaDB you can directly use REGEXP_SUBSTR see :mariadb.com/kb/en/mariadb/regexp_substr

pdem Over a year ago

Works in old version of Mysql (5.0.x for me) What some user might look for! (since old documentation are not online anymore)

Bernd Buffen Over a year ago

here are for old ducu dev.mysql.com/doc/index-archive.html

TechGuy · Accepted Answer · 2017-02-28 05:18:23Z

-8

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>

`

answered Feb 28, 2017 at 5:18

TechGuy

357 bronze badges

1 Comment

Sunil Harak Over a year ago

thanks for you response :) , but i want it for mysql

Collectives™ on Stack Overflow

extract substring from mysql column using regex

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related