0

I have a column called link, which can hold different types of link. I'd like to retrieve only those that have a urls, i.e. www.google.com, so that I can apply something. 

SELECT *
FROM UserAlert
WHERE Link = ...// check whether it's a url

Thanks for helping

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard77
  • 20,343
  • 46
  • 150
  • 252
  • If you are using SQL Server 2008 R2 or above try master data services and mdq.RegexIsMatch, this might help. – Mithrandir Oct 08 '13 at 20:35

1 Answers1

1

This is almost 100% likely to be a job better suited to the front-end application, not the database. It will require code execution on the server.

Here is a thread here on StackOverflow about url detection regexes, from which you can select any of a number of reasonably good expressions: What is the best regular expression to check if a string is a valid URL?

To use regexes in MSSQL, you need to first use MSSQL 2005 or later. Assuming that is the case... you have to wrap regex functionality in a custom CLR object, enable CLR interaction on your whole database, and then you can use that custom CLR object in your WHERE clause.

Here is a detailed article about doing exactly that with examples and step-by-step instructions.

I hope you're REALLY SURE that you want code execution to be part of your database. Good luck!

Community
  • 1
  • 1
djdanlib
  • 21,449
  • 1
  • 20
  • 29
  • Actually, it's just a scrip that I want to run so that I can do some clean up. Well I'll try to write a method in the front-end to do that then. – Richard77 Oct 08 '13 at 19:55
  • If you're just doing a one shot thing to do cleanup, writing a script that interfaces with SQL Server would be fine. You'll just have to do the regexes from your own code instead of SQL and probably wind up changing everything record by record. – djdanlib Oct 08 '13 at 19:59
  • Why is this better suited to the front end application? It has to run some where. If it is not a URL it is clearly more efficient to determine that on the server and not send the record. – paparazzo Oct 09 '13 at 13:56