How to validate a URL? 1

[MikeBronner]

Hello friends,

I am working on doing a database cleanup in preparation for some application migration. This entails making sure (as much as I can anyway) that each field contains the correct values. One of these field is for storing URLs. Is there any way I can check for valid URL format easily, for instance using PATINDEX?

Any help is appreciated!

Thanks

Take Care,
Mike

 

[AlexCuse]

You will want to look into using Regular Expressions for this. Here's a starting point for SQL 2000:

http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx

If you have 2005 at your disposal you may be able to use a CLR function.

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[mrdenny]

You will probably want to validate this in the front end and use a regular expression to do this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MikeBronner]

Thanks for the help, guys!

Alex, your link worked out perfectly. I modified his function somewhat, but it does exactly what I need. (I was hoping for a regex solution , btw., too!)

Denny, unfortunately validating on the front-end is out of the question for this project. Normally I would be in full agreement with you, but I'm working with legacy data that had an absolute crappy front-end. Now we're migrating to a better front-end that also supports validation, and we need to get all the past data into the new solution (as best possible).

Thanks again!

Take Care,
Mike