SQL - How to get certain characters from a column?-CodePudding

I have a table as:

id: Int,
URL: string,
//other fields

The URL field contains values like ["nytimes.com/live/", "prisma.io/docs/reference/api-reference/", "stackoverflow.com/questions",...]

How do I query to only get the domains of URLs ["nytimes.com", "prisma.io", "stackoverflow.com"...]

Right now I am doing it by fetching all the records and capturing the domain in server. I was wondering if there is a way to do this in SQL directly?

CodePudding user response：

SELECT SUBSTRING_INDEX(URL, '/', 1) FROM table ;

CodePudding user response：

In SQL Server,

CHARINDEX returns the position of a given character.
SUBSTRING returns the substring from the start of the string until the given index

Hence, combining CHARINDEX and SUBSTRING function, we can get the domain name.

Eg, if the domain name is google.com, the following query will return 'google'

SELECT SUBSTRING(URL, 1, CHARINDEX('.',URL)) FROM table_name

Other databases might have some variations.