How we can find domain name using MySQL and regular expression

by

In MySQL, regular expressions can match but not return substrings.

You can use SUBSTRING_INDEX:

SELECT  SUBSTRING_INDEX('www.example.com', "https://stackoverflow.com/", 1)

, however, it’s not protocol prefix safe.

If you are using a mix of prefixed and unprefixed URL‘s, use this:

SELECT  url RLIKE '^http://',
        CASE
        WHEN url RLIKE '^http://' THEN
                SUBSTRING_INDEX(SUBSTRING_INDEX(url, "https://stackoverflow.com/", 3), "https://stackoverflow.com/", -1)
        ELSE
                SUBSTRING_INDEX(url, "https://stackoverflow.com/", 1)
        END
FROM    (
        SELECT   'www.example.com/test/test' AS url
        UNION ALL
        SELECT   'http://www.example.com/test'
        ) q

More Related Contents:

  1. MYSQL Remove duplication [closed]
  2. How can i write in NodeJS with MongoDB the following MySQL Select?
  3. how sql join works on 3 tables with example
  4. MySQL treats password in script as database
  5. Search text in fields in every table of a MySQL database
  6. Which MySQL data type to use for storing boolean values
  7. MySQL join with where clause
  8. Implementing Comments and Likes in database
  9. SELECT * FROM multiple tables. MySQL
  10. What is the MySQL JDBC driver connection string?
  11. How to get the difference between two timestamps in seconds
  12. INSERT INTO…SELECT for all MySQL columns
  13. How to export SQL Server database to MySQL? [duplicate]
  14. MySQL: Select top n max values?
  15. MySQL search in comma list [duplicate]
  16. What is MySQL’s default ON DELETE behavior?
  17. Fetching UTF-8 text from MySQL in R returns “????”
  18. MySQL error 1022 when creating table
  19. MySQL – Persistent connection vs connection pooling
  20. How do I setup ASP.NET MVC 2 with MySQL?
  21. SELECT query return 1 row from each group
  22. MySql Single Table, Select last 7 days and include empty rows
  23. ERROR: Loading local data is disabled – this must be enabled on both the client and server sides
  24. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  25. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  26. Where does MySQL store database files on Windows and what are the names of the files?
  27. The web application [] appears to have started a thread named [Abandoned connection cleanup thread] com.mysql.jdbc.AbandonedConnectionCleanupThread
  28. Execute INSERT if table is empty?
  29. MySQL: Querying for unicode entities
  30. mysql order by, null first, and DESC after

Leave a Comment