SQL Server Using Charindex and Substring Functions to Peel out Data

The substring function on allows you to pull out a portion of a string. For example the if you want to pull out the domain name out of the url below, you’d run a query similar to that below.

select substring('www.testurl.com/testpage.htm', 1, 15)


So the substring functions starts at the beginning of the string (1), and moves a legnth of 15 characteers to grab all the data between the two. The result of the above query comes to “www.testurl.com”, and pealing off the page of the url. This works well when you know the size of the url, but not so when it’s dynamic. For example the this query won’t work against a different url.

select substring('www.newtesturl.com/testpage.htm', 1, 15)

The key is too make the second (Start) and third (Legnth) parameters dynamic. This is where the charindex comes into play. The charindex looks for a specific characteer and finds its position within the string. For example if you want to pull out of the url everything prior to the first occurance of a forward slash “/” you first need to find it’s characteer position. The statement below returns a result of 19, meaning the “/” is the 19th characteer from the start.

select charindex('/', 'www.newtesturl.com/testpage.htm', 1)


Before putting them together there is a trick. If we run the following query you’ll get a result which includes the forward slash.

select substring('www.newtesturl.com/testpage.htm', 1,
charindex('/', 'www.newtesturl.com/testpage.htm', 1))

In order to eliminate the forward slash we want to subtact 1 from the charindex portion giving you a statement the looks like so.

select substring('www.newtesturl.com/testpage.htm', 1,
charindex('/', 'www.newtesturl.com/testpage.htm', 1)-1)