For example:
Let’s say for string [abc](https://www.sqlskull.com), how would you extract the display name as “abc” and the URL as “https://www.sqlskull.com” separately?
Let’s create a sample table and insert some values into the table.
CREATE TABLE URLs (Id INT Identity(1,1), url_link VARCHAR(150))
INSERT INTO URLs
VALUES ('[google](https"//www.google.com)'),
('[sqlskull](https"//www.sqlskull.com)'),
('[wiki](https"//www.wikipedia.com)'),
('[abcxyz](https"//www.abcxyz.com)')
SELECT SUBSTRING(url_link, 2,
CHARINDEX( ']', url_link)-2) AS display_name,
SUBSTRING(url_link,
CHARINDEX( '(', url_link)+1,
LEN(url_link)-
CHARINDEX( '(', url_link)-1) AS URL from URLs
SUBSTRING(url_link, 2, CHARINDEX( ']', url_link)-2)
It uses the CHARINDEX function CHARINDEX(‘]’, url_link) which finds the
the position of the closing “]” bracket.
Next, it uses the SUBSTRING function SUBSTRING(url_link, 2, CHARINDEX(‘]’, url_link) – 2) which extracts the substring starting from the second character up to the character before the “]” and returns the display name.
To extract the URL:
SUBSTRING(url_link, CHARINDEX('(', url_link) + 1,
LEN(url_link) - CHARINDEX('(', url_link) - 1)
It uses the CHARINDEX fuction CHARINDEX(‘(‘, url_link) which finds the position of the opening “(” bracket.
Then LEN function LEN(url_link) returns the total length of the string value url_link.
Next, SUBSTRING(url_link, CHARINDEX(‘(‘, url_link) + 1, LEN(url_link) – CHARINDEX(‘(‘, url_link) – 1) extracts the substring starting from the character after ( up to the end of the string minus 1 character, which returns the URL.
Here, is the output of above query.
Recommended for you
