Need help writing regex with varying multiple lines and pull SUBSTRING in MySQL

Question

I'm trying to pull data of Shipping info from a string containing line breaks. The shipping info maybe between 3 and 5 lines. Here's a few examples:

    /* Just need from here */
Ship NEXT DAY PM to:
John Doe
Address Street
Sweet, NC 55555
    /* to here */
Email: john@doe.net
Phone: 555-555-555

Ship NEXT DAY AM to:
John Doe
Address Street
Apt. 555
Sweet, NC 55555

Email: john@doe.net
Phone: 555-555-555

Ship NEXT DAY PM to:
John Doe
c/o Jane Doe
Address Street
Apt. 555
Sweet, NC 55555

Email: john@doe.net
Phone: 555-555-555

UPDATE

I finally figured out a solution for the regex. Also one was mentioned in comments that seems to work well. Not sure which would be better based on any "why" but here they are:

Ship (.)+ to\:((\n)(.)+){1,5}

Ship (.)+ to\:[\s\S]*?(?=\nEmail)

Now I've got a whole new issue. Turns out, MySQL REGEXP does not function as I expected (which I would have known had I bothered to read rather than skim API). So Now I'm trying to pull what I just regexed out of a substring IF it exist. The IF part I got, I simply use the REGEX to check if Ship To is found and if not NULL then start on substring extraction. The Substring extraction is where I'm stuck now as it's proving rather tedious in using LOCATE in multiple combinations to try and get exact character position. Anyone know a quick and easy way? I'll post what I'm working on as soon as I don't get an ERROR, lol.

So far working the following into If statement:

MID(`Notes`, (LOCATE(' to:\n', `Notes`) + 5), (LOCATE('\n\nEmail:', `Notes`) - LOCATE(' to:\n', `Notes`) - 5)) as ShipAddress

How about: `Ship (.)+ to\:[\s\S]*?(?=\nEmail)`? [Here](http://regexhero.net/tester/?id=dfaceb56-f7a3-4ae0-b576-95425b09f32c)'s a demo. — Alex Filipovici, Nov 04 '13 at 15:48
TY @AlexFilipovici that seems to work just as well. Now I've a new issue though. As per my question update, using said expression to extract substring using a MYSQL call. — SpYk3HH, Nov 04 '13 at 15:51
Maybe you could use the [lib_mysqludf_preg](https://github.com/mysqludf/lib_mysqludf_preg#readme) UDF which is also referenced here: [http://stackoverflow.com/a/2742764/674700](http://stackoverflow.com/a/2742764/674700). — Alex Filipovici, Nov 04 '13 at 16:04

score 1 · Answer 1 · edited May 23 '17 at 12:23

1

The following pattern should work:

Ship (.)+ to\:[\s\S]*?(?=\nEmail)

Here's explained what it does.

For the MySQL part, maybe you could use the lib_mysqludf_preg UDF which is also referenced here: https://stackoverflow.com/a/2742764/674700.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 04 '13 at 16:07

Alex Filipovici

31,789
6
54
78

score 0 · Accepted Answer · answered Nov 05 '13 at 14:18

My Final Solution:

While the suggestion of the extra lib was probably a good way to go, I'd rather just go with what's available. In the end I made use of the Regex and a simple CASE Statement as follows:

CASE
    WHEN `Notes` REGEXP 'Ship (.)* to\:((\n)(.)+){1,5}'
    THEN MID(`Notes`, (LOCATE(' to:\n', `Notes`) + 5), (LOCATE('\n\nEmail:', `Notes`) - LOCATE(' to:\n', `Notes`) - 5))
END as 'ShipAddress',

Of course this requires knowing the exact layout of each note being parsed. Luckily I wrote that code to and I do know the exact layout saved to the DB every time. :)

Pity this is so limited to knowing the char position. Thanks though — New Alexandria, Oct 23 '15 at 15:24

Need help writing regex with varying multiple lines and pull SUBSTRING in MySQL

2 Answers2

My Final Solution: