Sean Lange (6/27/2013)
DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).This is a regular expression. All it does is checks if the the string has any number in it (0,1,2,3,4,5,6,7,8,9).
Hmmm... Technically I'd say it is Microsoft SQL's (somewhat limited) proxy for a Regular Expression. And I don't think they refer to it as that. I believe BOL always refers to it as a pattern (http://msdn.microsoft.com/en-us/library/ms179859.aspx).
The RegEx for this case would be something like: [0-9]{1,30}$ (or ^[0-9]{1,30}$ depending on whether you're doing a positive or negative test).
{1,30} specifies the overall length of the allowable characters.
There are add-ins to SQL (e.g., via CLR or the SQL Sharp library[/url]) that would allow validation by RegEx.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St