2

I would like help formulating a regex which I can query MySQL5.6 with to get a list of hostnames which are in proper FQDN format.

I found the following regex doing an online search which closely matches what I'm looking for https://www.regextester.com/98986 "^(([a-zA-Z0-9]|[a-zA-Z0-9][a-zA-Z0-9\-]*[a-zA-Z0-9])\.){2,}([A-Za-z0-9]|[A-Za-z0-9][A-Za-z0-9\-]*[A-Za-z0-9]){2,}$"

When I tested this regex in python, it gave me the results I expected. 

>>> reg = "^(([a-zA-Z0-9]|[a-zA-Z0-9][a-zA-Z0-9\-]*[a-zA-Z0-9])\.){2,}([A-Za-z0-9]|[A-Za-z0-9][A-Za-z0-9\-]*[A-Za-z0-9]){2,}$"
>>> hostname ='abc.tmp.company.com'
>>> print(re.search(reg,hostname))
<_sre.SRE_Match object at 0x109da5880>
>>> res = re.search(reg,hostname)
>>> res.group(0)
'abc.tmp.company.com'
>>> invalid_hostname ='invalid'
>>> print(re.search(reg,invalid_hostname))
None
>>> invalid_hostname2 = 'in-test-stg'
>>> print(re.search(reg,invalid_hostname2))
None

But when I queried MySQL, it returned names which were in non-FQDN format too i.e. single word names such as 'invalid' were returned from the DB.

select name from MYTABLE 
where name RLIKE '^(([a-zA-Z0-9]|[a-zA-Z0-9][a-zA-Z0-9\-]*[a-zA-Z0-9])\.){2,}([A-Za-z0-9]|[A-Za-z0-9][A-Za-z0-9\-]*[A-Za-z0-9]){2,}$'

I've reviewed https://dev.mysql.com/doc/refman/5.6/en/regexp.html and https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/ but there might be something I'm missing.

How can I get that regular expression to work in MYSQL with the RLIKE operator? Alternatively, what regular expression can I use when querying MYSQL natively to return names which are in FQDN form?

Barmar
  • 741,623
  • 53
  • 500
  • 612
skydive
  • 21
  • 2
  • 1
    You need to double the backslashes in SQL strings. – Barmar Sep 07 '19 at 00:42
  • It also may not allow escaping `-` inside character sets. You don't need to escape it if it's the first or last character. – Barmar Sep 07 '19 at 00:43
  • Great, thanks @Barmar I added the extra backslash and now I'm closer to what I'm looking for! ``` "^(([a-zA-Z0-9][a-zA-Z0-9\-]*[a-zA-Z0-9])\\.){2,}([A-Za-z0-9]|[A-Za-z0-9][A-Za-z0-9\-]*[A-Za-z0-9]){2,}$" ``` Now we get FQDN-like results. The final step is that I need to update the regex not to give me results matching IP Addresses, i.e. all digits so I'm working through that exercise now as well. If anyone has any pointers as I'm working on that, it would help. – skydive Sep 07 '19 at 01:01
  • Can't use `{,2}` before version 8.0. – Rick James Sep 07 '19 at 01:12
  • And the 8.0 documentation doesn't actually mention that it's allowed. You can write `{2,}` in either version. – Barmar Sep 08 '19 at 20:53

0 Answers0