CODEWITHSUNDEEP
mysqlsql
RoadRunner
RoadRunner

Reputation: 26335

How to select string between characters?

I made this table:

Table (Websites)

WebsiteID | WebsiteName
2324442     'http://www.samsung.com/us/'
2342343     'https://www.microsoft.com/en-au/windows/'
3242343     'http://www.apple.com/au/iphone/'

And I want to be able to SELECT the domain names from this table.

Something like this:

WebsiteName
'www.samsung.com'
'www.microsoft.com'
'www.apple.com'

Is there a string method I can use for this? Like splitting the string between // and /.

Upvotes: 1

Views: 63

Answers (2)

AT-2017
AT-2017

Reputation: 3149

You can even use the following:

SELECT WebsiteID , WebsiteName
(CHARINDEX ( '//', WebsiteName, 1 ) + 1), -- Position of the double slashes
CHARINDEX( '/', REVERSE (WebsiteName ), 1), -- Position of the last single slash
SUBSTRING(WebsiteName, (CHARINDEX ( '//' ,  WebsiteName, 1 ) + 2), CHARINDEX( '/', REVERSE (WebsiteName ), 1) ) -- Final string
FROM Table

Upvotes: 1

sagi
sagi

Reputation: 40491

You can use SUBSTRING_INDEX() :

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(websiteName, '//', -1),
                   '/', 1)
FROM table

Upvotes: 3

Related Questions