|
Hi I asked this on stack overflow but no answer as yet... i'm trying to create a script that will allow me to use different regex to search for different values in dbs across an instance... i.e. phone numbers, card numbers etc. I think i've nearly cracked it I just need some help, i'm not sure the regex will work properly... pasted my code below... thanks!
(comments are locked)
|
|
The code you have posted will look into the same table x times where x = number of columns in a table and I bet that all of your columns is not indexed or the indexes can't be used. That means that you'll get x table scans for y tables. Thats a lot of work. You probably want a single scan/seek. Try to create a concatenated string and execute the string only once per table. Don't quite understand what you mean, or how I would implement that... would you be able to edit my code or provide an example? Thanks for your help...
Oct 17 '11 at 06:21 AM
jhowe
(comments are locked)
|
|
You can't use regex expressions like that in TSQL. You would have to create a regex function in CLR - search here for questions about regex - you will get a lot of good answers http://ask.sqlservercentral.com/search/?q=regex&Submit=search&t=question As Håkan has said, this isn't going to run nicely, but as it stands, it isn't going to run at all! I haven't tried it myself, but if you install Master data services, you'll get support for regex. I don't know the syntax, but you can try http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Oct 17 '11 at 06:58 AM
Håkan Winther
Yes these are CLR functions supplied with MDS, whether or not you can lift and use them in non-MDS is another matter, but essentially it's the same as writing your own. There still is no TSQL support for regex.
Oct 17 '11 at 07:31 AM
Kev Riley ♦♦
(comments are locked)
|

