Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chicom
Starting Member
1 Post |
 Posted - 2006-09-08 : 16:18:18
|
| Currently I use javascript in an ASP page to search a large memo field and extract any URLs by means of a RegEx. This seems like a waste of resources to do this everytime a page is displayed. Is it possible to use the RegEx with a SQL trigger which will extract any URLs from the memo field after an SQL insert/update and put the URLS in a new field in the same row?I have the RegEx code and I know how to do the trigger but I don't know how to put them together! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-08 : 16:35:53
|
| I like the idea of saving off your URLs to a seperate place. I would not put that load on a trigger though. How about a trigger that simply populates a queue with the ID of inserted/updated row. Then a seperate process can (off line) perform the extraction of urls and update a [status] column of the queue table. I would also save the URLs to a new seperate table so that can have many urls (rows) per parentID.Be One with the OptimizerTG |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-08 : 17:40:27
|
| Unfortunately SQL Server does not support regular expressions. You can use them with CLR code in SQL Server 2005. In SQL Server 2000 you'd have to use an extended stored proc or if you do things the way TG suggests the separate process could use client code to run the regular expressions and update the URL field or records. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-08 : 19:17:43
|
| Don't know why you're against regex's in JavaScript, it's one of the better regex engines out there except for Perl. The Snitz forum code (SQLTeam uses it) does regex's on the server in ASP VBScript (which IMHO is less efficient than letting JS do it on the client) and it works very well.In fact, you can use client-side JS to parse the URLs and put them into a hidden field or fields, which you can then pass to your SQL procedure as regular variables when the web form submits back to the server. That way you get the best of both worlds: a one-time parse with a good regex engine that doesn't require any extra server-side processing. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-08 : 21:29:12
|
quote:
Originally posted by robvolk
In fact, you can use client-side JS to parse the URLs and put them into a hidden field or fields, which you can then pass to your SQL procedure as regular variables when the web form submits back to the server. That way you get the best of both worlds: a one-time parse with a good regex engine that doesn't require any extra server-side processing.
Great idea! (Thats why they pay Rob the big-bucks)Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-09 : 04:52:15
|
Never thought to off load the IIS RegEx load to the client's PC- but I *do* like that idea.I investigated a slow-down in IIS the other day and discovered that IIS was taking 10 seconds to run a RegEx - only only a couple of K of data  I would up "weakening" the RegEx from\{[A-Za-z]([A-Za-z0-9_]*[A-Za-z0-9])*.DEFAULT\}to\{[A-Za-z][A-Za-z0-9_]*[A-Za-z0-9]\.DEFAULT\}which solved the problem, but offloading that to the Client would be a better idea ...Kristen |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
Extract urls from memo field