Click here to Skip to main content
15,936,802 members
Home / Discussions / Regular Expressions
   

Regular Expressions

 
QuestionMySQL Select query that chooses something dot something between certain underscores Pin
User 1447141414-Sep-23 6:12
User 1447141414-Sep-23 6:12 
AnswerRe: MySQL Select query that chooses something dot something between certain underscores Pin
RedDk14-Sep-23 6:36
RedDk14-Sep-23 6:36 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
User 1447141418-Sep-23 4:28
User 1447141418-Sep-23 4:28 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
RedDk18-Sep-23 7:23
RedDk18-Sep-23 7:23 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
User 1447141418-Sep-23 23:16
User 1447141418-Sep-23 23:16 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
Richard MacCutchan19-Sep-23 1:45
mveRichard MacCutchan19-Sep-23 1:45 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
User 1447141419-Sep-23 4:46
User 1447141419-Sep-23 4:46 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
Richard MacCutchan19-Sep-23 5:30
mveRichard MacCutchan19-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.

GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
User 1447141420-Sep-23 2:17
User 1447141420-Sep-23 2:17 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
Richard MacCutchan20-Sep-23 2:31
mveRichard MacCutchan20-Sep-23 2:31 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
RedDk21-Sep-23 7:17
RedDk21-Sep-23 7:17 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
Richard MacCutchan21-Sep-23 23:07
mveRichard MacCutchan21-Sep-23 23:07 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
RedDk22-Sep-23 9:44
RedDk22-Sep-23 9:44 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
RedDk19-Sep-23 8:17
RedDk19-Sep-23 8:17 
GeneralRe: MySQL Select query that chooses something dot something between certain underscores Pin
Richard MacCutchan19-Sep-23 22:09
mveRichard MacCutchan19-Sep-23 22:09 
AnswerRe: MySQL Select query that chooses something dot something between certain underscores Pin
jschell14-Sep-23 10:55
jschell14-Sep-23 10:55 
QuestionExclude already masked data in regex Pin
NathanAsh25-Aug-23 11:14
NathanAsh25-Aug-23 11:14 
AnswerRe: Exclude already masked data in regex Pin
Terry R 202325-Aug-23 12:48
Terry R 202325-Aug-23 12:48 
GeneralRe: Exclude already masked data in regex Pin
NathanAsh28-Aug-23 5:32
NathanAsh28-Aug-23 5:32 
QuestionRegEx remove duplicate need help Pin
manoo885-Aug-23 13:04
manoo885-Aug-23 13:04 
GeneralRe: RegEx remove duplicate need help Pin
Member 106011917-Aug-23 12:04
professionalMember 106011917-Aug-23 12:04 
AnswerRe: RegEx remove duplicate need help Pin
Andre Oosthuizen7-Aug-23 23:27
mveAndre Oosthuizen7-Aug-23 23:27 
AnswerRe: RegEx remove duplicate need help Pin
jschell11-Aug-23 10:49
jschell11-Aug-23 10:49 
GeneralRe: RegEx remove duplicate need help Pin
trønderen11-Aug-23 12:21
trønderen11-Aug-23 12:21 
GeneralRe: RegEx remove duplicate need help Pin
jschell14-Aug-23 6:01
jschell14-Aug-23 6:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.