• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • Last Modified:

Substring function

Hello
I have a table with a field Refrrer_URL Nvarchar(500)  ... the field stored URLS
I need to get ONLY the website url without any additional details .
in other words let's say hte data s follow

Referrer_URL
--------------------
http://www.Mywebsitexxxx.com/hello/h.aspx
www.hiswebsitexxxx.net/contacts/cotactus.html
http://ourwebsitexxxx.org/location.html
-------------------------------

I need to select the date to be

http://www.Mywebsitexxxx.com
www.hiswebsitexxxx.net
http://ourwebsitexxxx.org
0
ali_alannah
Asked:
ali_alannah
1 Solution
 
Patrick MatthewsCommented:
Try:

SELECT Refrrer_URL,
    LEFT(Refrrer_URL, PATINDEX('%[A-Z]/%', Refrrer_URL + '/') - 1) AS Shortened
FROM SomeTable
WHERE Refrrer_URL + '/' LIKE '%[A-Z]/%

Open in new window

0
 
ZberteocCommented:
Try this:

SELECT 
	Refrrer_URL,
	case 
		when Refrrer_URL like '%.com%' then 
			LEFT(Refrrer_URL, PATINDEX('%.com%',Refrrer_URL)+4)
		when Refrrer_URL like '%.net%' then 
			LEFT(Refrrer_URL, PATINDEX('%.net%',Refrrer_URL)+4)
		when Refrrer_URL like '%.org%' then 
			LEFT(Refrrer_URL, PATINDEX('%.org%',Refrrer_URL)+4)
		-- when ... (do the same with any domain you expect)
		else
			Refrrer_URL
	end as ulr
FROM 
	SomeTable

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now