Link to home
Start Free TrialLog in
Avatar of Ali Saad
Ali SaadFlag for Kuwait

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zberteoc
Zberteoc
Flag of Canada image
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