Regular Expressions

Re: MySQL Select query that chooses something dot something between certain underscores

14-Sep-23 6:36

One of these then:
\b([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}\b
^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$
\b((xn--)?[a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}\b
\b((?=[a-z0-9-]{1,63}\.)(xn--)?[a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,63}\b
\b((?=[a-z0-9-]{1,63}\.)[a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,63}\b
Footnote: these make no sense of leading BLANK as your requirement states but I throw them out there anyway for your testing pleasure.

modified 14-Sep-23 14:11pm.

User 1447141418-Sep-23 4:28

Re: MySQL Select query that chooses something dot something between certain underscores

18-Sep-23 4:28

Unfortunately none of them worked at all.

REGEXP_SUBSTR(field_name, '^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}$') immediately timed out (#3699 - Timeout exceeded in regular expression match), and the rest just returned NULL for everything.

Any idea why?

RedDk18-Sep-23 7:23

Re: MySQL Select query that chooses something dot something between certain underscores

18-Sep-23 7:23

Well, first of all those regex were std::regx not MySQL.

What exactly have timing out and error #3699 to do with this "SELECT" query? I'm not sure why none of them work because I'm not sure how you're leveraging this database. REGEX_SUBSTR? Is that a function?

Probably Oracle, he said.

I know nothing about Oracle, sorry.

User 1447141418-Sep-23 23:16

Re: MySQL Select query that chooses something dot something between certain underscores

18-Sep-23 23:16

There's nothing about Oracle here other than the company name. It's just a standard MySQL command, see https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr[^] and MySQL regexp_substr() function - javatpoint[^].

All I asked was which input to type there to get the domain name from a column that puts the domain between underscores (or just end of line) and after at least 2 underscores (examples in the OP).

Richard MacCutchan19-Sep-23 1:45

Re: MySQL Select query that chooses something dot something between certain underscores

19-Sep-23 1:45

I just tried a quick test at RegExr: Learn, Build, & Test RegEx[^] and the following regex finds all the web addresses in the set below (shown in bold). The only thing I cannot correct is that it includes the leading underscore. However it may be a starting point to help you (I have not actually tried it with MySQL).

_[a-zA-Z]*\.[a-zA-Z]*|\.[a-zA-Z].

Something_Else_Type_XY_Z_26.04.23_website.com
Something_Else_Type_XY_Z_website.co.uk_26.04.23
Something_Else_Type_XY_Z_26.04.23_website.com_Comment
Something_website.com_Else_Type_XY_Z_26.04.23_Comment
Something_Else_Type_XY_Z_26.04.23_Comment
Something_Else_Type_XY_dfd6869_3_21.12.22_website.com_ZU

User 1447141419-Sep-23 4:46

Re: MySQL Select query that chooses something dot something between certain underscores

19-Sep-23 4:46

Unfortunately REGEXP_SUBSTR(field_name, '_[a-zA-Z]*\.[a-zA-Z]*|\.[a-zA-Z]') just gives me the first 2 letters of the field_name ("So" in this case).

Richard MacCutchan19-Sep-23 5:30

Re: MySQL Select query that chooses something dot something between certain underscores

19-Sep-23 5:30

Yes, I got similar results, which does not make a lot of sense. However, on reading the documentation (https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr[^]) closely I discovered why. Hidden down near the bottom of the page is the following:

Quote:
To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. For example, to match the string 1+2 that contains the special + character, only the last of the following regular expressions is the correct one:

So I tried with that, and a slight modification to my original pattern, and successfully extracted the fields as shown in the following results:

mysql> SELECT REGEXP_SUBSTR(Title, '_[a-zA-Z]+(\\.[a-zA-Z]+)+') from Books;         
+---------------------------------------------------+
| REGEXP_SUBSTR(Title, '_[a-zA-Z]+(\\.[a-zA-Z]+)+') |
+---------------------------------------------------+
| NULL                                              |
| NULL                                              |
| NULL                                              |
| NULL                                              |
| _website.com                                      |
| NULL                                              |
| _website.com                                      |
| _website.com                                      |
| _website.co.uk                                    |
| _website.com                                      |
| NULL                                              |
| NULL                                              |
+---------------------------------------------------+

The NULL entries are records that do not contain a web address.

[EDIT]
I just tried without the leading underscore in the regular expression and added some numbers into the website names and got the following:

mysql> SELECT REGEXP_SUBSTR(Title, '[a-zA-Z][[:alnum:]]+(\\.[a-zA-Z]+)+') from Books;
+-------------------------------------------------------------+
| REGEXP_SUBSTR(Title, '[a-zA-Z][[:alnum:]]+(\\.[a-zA-Z]+)+') |
+-------------------------------------------------------------+
| NULL                                                        |
| NULL                                                        |
| NULL                                                        |
| NULL                                                        |
| website.com                                                 |
| NULL                                                        |
| google.co.it                                                |
| website99.com                                               |
| Bet365.co.uk                                                |
| website.com                                                 |
| NULL                                                        |
| NULL                                                        |
+-------------------------------------------------------------+
12 rows in set (0.01 sec)

So it would appear to be simpler than I thought.

[/EDIT]

modified 20-Sep-23 4:18am.

User 1447141420-Sep-23 2:17

Re: MySQL Select query that chooses something dot something between certain underscores

20-Sep-23 2:17

Did you test according to my examples? What about:

Something_website.com_Else_Type_XY_Z_26.04.23_Comment => NULL (due to not having 2 preceding underscores first)

Your regex returns website.com there instead of NULL.

Richard MacCutchan20-Sep-23 2:31

Re: MySQL Select query that chooses something dot something between certain underscores

20-Sep-23 2:31

No, I am afraid I did not work that one out; I leave that as an exercise for you.

RedDk21-Sep-23 7:17

Re: MySQL Select query that chooses something dot something between certain underscores

21-Sep-23 7:17

Concur here, using RegexBuddy and this string:

[a-zA-Z][[:alnum:]]*\.[a-zA-Z]*|\.[a-zA-Z]

Nothing but net (colloquial expression eliding formal disdain without turning away in disgust from self-realized talentlessness). Thanks for this.

modified 21-Sep-23 17:54pm.

Richard MacCutchan21-Sep-23 23:07

Re: MySQL Select query that chooses something dot something between certain underscores

21-Sep-23 23:07

MySQL's RE engine has one or two quirks of its own(after all, why make it easy?). The only part I could not figure out is how to ensure at least two underscores in the earlier part of the string. I bet @OriginalGriff or @RichardDeeming could do it with their eyes closed.

RedDk22-Sep-23 9:44

Re: MySQL Select query that chooses something dot something between certain underscores

22-Sep-23 9:44

Using RegexBuddy (yeah, I guess I am falling into a hole here) the POSIX notation is perfectly acceptable, hence your [:alnum:]. I can substitute [:alpha:] there as well and I suspect others in the list are good actors. My brain infarct occurs though when I use Herr Gevertz' table of translations and try to replace the POSIX with his ASCII ([a-zA-Z0-9]) or for that matter UNICODE ([\p{L}\p{Nl}\p{Nd}]), neither of which work in his interface. Grand tool, RegexBuddy ... it's one of those feature-rich interfaces which swim with example/sample in the help volume.

My only complaint is that searching through documentation for words has no highlight of the found set so I've got to print out the page and use my pdf search to locate all instances!

RedDk19-Sep-23 8:17

Re: MySQL Select query that chooses something dot something between certain underscores

19-Sep-23 8:17

Ok, (didn't read the doc link but thanks for that). Using your new regex:

_[a-zA-Z]*\.[a-zA-Z]*|\.[a-zA-Z]

on the sample of your OP:

1. Something_Else_Type_XY_Z_26.04.23_website.com => _website.com
2. Something_Else_Type_XY_Z_website.com_26.04.23 => _website.com
3. Something_Else_Type_XY_Z_26.04.23_website.com_Comment => _website.com
4. Something_website.com_Else_Type_XY_Z_26.04.23_Comment => NULL 
5. Something_Else_Type_XY_Z_26.04.23_Comment => NULL 
6. Something_Else_Type_XY_dfd6869_3_21.12.22_website.com_ZU = _website.com

Which isn't quite what you wanted (because of that leading "_"). And as for RM's take on it, as-written (without any _[a-zA-Z]*\.) THAT string returns no matches here either (I use a tool called RegexBuddy and after yesterday's experience, with MySql selected as the input language not std::regex).

So although I can confirm today's discovery is close it'll take me some more sleuthing to run down that leading character ascii 95 ...

{EDIT]
I'm seeing a warning when I try to tackle that underscore using "shorthand character classes" \w to the tune of "MySql doesn't support blah blah blah" so ... this could be a while.
[END EDIT]

modified 19-Sep-23 14:52pm.

Richard MacCutchan19-Sep-23 22:09

Re: MySQL Select query that chooses something dot something between certain underscores

19-Sep-23 22:09

See my update to yesterdays answer above.

jschell14-Sep-23 10:55

jschell

14-Sep-23 10:55

You should keep in mind that this is going to be really slow when compared to other types of searches.
So if you are using large volumes of data and/or many searches you should keep that in mind.

You should also keep in mind that there are valid domain names that will have more than one dot (period).

Exclude already masked data in regex

NathanAsh25-Aug-23 11:14

NathanAsh

25-Aug-23 11:14

hi

I am trying to mask 3 to 8 digit numbers alone exclusively, by excluding already masked data with another regex pattern.
For example it should not match the masked patterns like
[xX\-\*\d{1,4}] , but match the pattern \d{3,8}

Can you please help with the regex pattern to achieve this?
I have tried something like this, but its not matching for the pattern \d{3,8}

(?<=(?<!x\-\*)\d{4})\d{3,8}

Input :

1234
x123412345678
-123412345678
*123412345678
123
1234
12345
123456
1234567
12345678

Expected masking

****
x1234****
-1234****
*1234****
****
****
****
****
****
****

Re: Exclude already masked data in regex

Terry R 202325-Aug-23 12:48

Terry R 2023

25-Aug-23 12:48

If I were to use Notepad++ then the find regex would be:

([x\-*]\d{1,4})?(\d{3,8})

So it ties the 1-4 numbers immediately following a x (or X as Notepad++ normally case insensitive), a - (this is a meta character so needs the escape char to make it a normal char) or a *. Having them in a group with a ? allows for non capture if the line doesn't start with the x, - or *. The second group captures between 3 and 8 numbers.

Now this all works as expected in Notepad++, however sometimes these don't translate well across the various regular expression engines. As you haven't provided that info I can't give you an answer specific to your need.

Also I note you seem to want to "mask" the 3-8 digits. As it stands my 2nd group will just support replacing with a fixed number of "*", so some adjustment would be needed.

Hopefully this has helped you though to continue with a final solution.

Terry

Re: Exclude already masked data in regex

NathanAsh28-Aug-23 5:32

NathanAsh

28-Aug-23 5:32

hey Terry,

Thanks for the suggestion. I am trying to achieve this patter replacement in java
This one I thought of it but it is not working out for the input
*1234
x1234
-1234
these kind of inputs should not be masked and output should be as it is *1234 / x1234 / -1234

whatever you have given works fine for
*12345678
x12345678
-12345678

RegEx remove duplicate need help

manoo885-Aug-23 13:04

manoo88

5-Aug-23 13:04

Hi, not good at RegEx and trying to remove some duplicate values.
data is in CSV, part of it looks like this:

"/content/7/66345/images/590009.jpg , "/content/7/66345/images/590009.jpg , "/attachments/fe519c1e91c5e4983a70a2512fd5788b.jpg , "/content/7/66345/images/590009.jpg , "/content/7/66345/images/590009.jpg , "/attachments/4956e4fe56b59135c086605c9gyye.png
"/content/1/3968663/images/856609.jpg , "/attachments/086605c7c6e4fe56b59135c11b.jpg , "/content/1/3968663/images/856609.jpg , "/attachments/086605c7c6e4fe56b59135c11b.jpg
"/content/1/1458767/images/856657.jpg
"/content/1/1448511/images/856373.jpg

I am trying the following using Notepad++:
\w+\.+jpg|\w+\.+png(?:^|\G)(\b\w+\b),?(?=.*\1)

it does select one image by one image when clicking find, but I am not sure how to delete duplicates,
when I replace it with empty, it removes all, I want to let the first image remain and delete the duplicates, Anyone can help me with the code, please?
I don't want to remove the line, because it could mess with the CSV file, removing the extension of the duplicate image is OK,
Thanks for your help.

Member 106011917-Aug-23 12:04

Member 10601191

7-Aug-23 12:04

Hi, requesting some clarification:

Given the sample input, please post the expected output, that's to avoid any misunderstanding by me. I could guess but prefer not too.

Also, must this be done on Notepad++ (if yes - why ?), and on which OS ?.

thks

Andre Oosthuizen7-Aug-23 23:27

Andre Oosthuizen

7-Aug-23 23:27

The regex you provided is close, but there are a few modifications needed to achieve your desired result. Here's the correct regex and how you can use it in Notepad++ -

Regular

("\/.*?\.(?:jpg|png))\s*,\s*(?=.*\1)

I have tested this regex in Notepad++ using the following steps -

1) Open your CSV file in Notepad++.
2) Press Ctrl + H to open the "Find" dialog.
3) In the "Find what" field, enter the regex: ("\/.*?\.(?:jpg|png))\s*,\s*(?=.*\1).
4) Leave the "Replace with" field empty.
5) In the "Search Mode" section, select "Regular expression".
6) Click on "Replace All".

Make sure to have a backup of your data before performing any find and replace operations, just in case...

jschell11-Aug-23 10:49

jschell

11-Aug-23 10:49

Presumably your expectation is the following
1. The entire row is duplicated.
2. The duplicated row immediately follows the first row.

Otherwise I doubt regex is the way to go.

trønderen11-Aug-23 12:21

trønderen

11-Aug-23 12:21

If the ordering of the rows is insignificant you can simply sort the lines to collect the duplicate rows together. (And if necessary, sort again on a key field after you have completed.)

But if you want to remove entire duplicated rows (lines), are you serious about using a regex to compare entire text lines for being identical? That can't be! But from the OP's first post, I cannot see what he intends to compare, and what he intends to remove.