Validate URL

Question

Validate URL

rash3554

SSCommitted

Points: 1984
More actions
March 19, 2015 at 2:37 pm

#315853

Hello,
I have several URLS in a table that look like this
http://www.g.com.com
http://www.y
http://www.google.com.kr
This is the SQL I wrote
select * from dbo.URLTable where url not like '%[^a-zA-Z0-9]%.[a-z][a-z]%'
but couldn't catch the above cases.
I tried using SQL CLR function REGEXParse but was unsuccesful. Can somebody provide me hints?
Thanks
MR

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rash3554 (3/19/2015)
Hello,
I have several URLS in a table that look like this
http://www.g.com.com
http://www.y
http://www.google.com.kr
This is the SQL I wrote
select * from dbo.URLTable where url not like '%[^a-zA-Z0-9]%.[a-z][a-z]%'
but couldn't catch the above cases.
I tried using SQL CLR function REGEXParse but was unsuccesful. Can somebody provide me hints?
Thanks
MR

You will NEVER get this correct in t-sql. It is far too complicated. For example, there is absolutely nothing wrong with the first example you posted. If the base domain is com.com and there is a subdomain of g this is perfectly valid.

I would look into a decent RegEx online. For example look at this page dedicated to finding a regex that can do this 100%. https://mathiasbynens.be/demo/url-regex

In other words, you need to find yourself a regex that captures most of the invalid stuff and move on.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

I'm sure you could write some kind of CLR function that would go ping your URL's, that'll tell you whether they're valid or not 😀

Trying to rely on regular expressions for this kind of thing is prone to either being too loose or too strict.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'd go with the ping option as a validation before inserting in the table.

Would this be a valid URL http://www.thisisaninvalidurl.com? It has the correct format but won't get you anywhere.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 4

ZZartin (3/19/2015)
I'm sure you could write some kind of CLR function that would go ping your URL's, that'll tell you whether they're valid or not 😀
Trying to rely on regular expressions for this kind of thing is prone to either being too loose or too strict.

I built one for CLR a while ago as a proof of concept:

http://www.sqlservercentral.com/Forums/FindPost1382517.aspx

you can grab the text results and see oif there is an 404 error or if it raised an error or whatever

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Luis Cazares (3/19/2015)
I'd go with the ping option as a validation before inserting in the table.
Would this be a valid URL http://www.thisisaninvalidurl.com? It has the correct format but won't get you anywhere.

Using ping is probably more likely to return false negatives than it is to find invalid urls. for example you can't ping microsoft.com, but you can ping http://www.microsoft.com. Either of those entered into ANY browser are perfectly fine.

Also, some perfectly valid servers will not send a reply to a ping so those would also possibly indicate a false negative.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Lowell (3/19/2015)
ZZartin (3/19/2015)
I'm sure you could write some kind of CLR function that would go ping your URL's, that'll tell you whether they're valid or not 😀
Trying to rely on regular expressions for this kind of thing is prone to either being too loose or too strict.
I built one for CLR a while ago as a proof of concept:
http://www.sqlservercentral.com/Forums/FindPost1382517.aspx
you can grab the text results and see oif there is an 404 error or if it raised an error or whatever

Excellent. I was remembering this thread but wasn't having any luck finding it. Awesome.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 7

Sean Lange (3/19/2015)
Luis Cazares (3/19/2015)
I'd go with the ping option as a validation before inserting in the table.
Would this be a valid URL http://www.thisisaninvalidurl.com? It has the correct format but won't get you anywhere.
Using ping is probably more likely to return false negatives than it is to find invalid urls. for example you can't ping microsoft.com, but you can ping http://www.microsoft.com. Either of those entered into ANY browser are perfectly fine.
Also, some perfectly valid servers will not send a reply to a ping so those would also possibly indicate a false negative.

Yeah ping might have been the wrong word, I meant anything that will verify that a url can be resolved for example ping will resolve microsoft.com and you can see that even though you don't get a reply.

And it looks like Lowell posted a solution that will work just fine for that.