Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server
select 'yes'
where TRY_CONVERT(decimal(29, 9), @SearchVal) IS NOT NULL
Good solution and would probably be fine. Note this though:
SELECT TRY_CONVERT(decimal(29, 9), $)
The pattern that he is trying to match excludes strings that represent negative numbers, which the TRY_CONVERT will not. Of course, it's easy to modify the WHERE clause to only return positive (or non-negative numbers) or to modify the pattern to match negative numbers.
Drew
Agreed.
I think your solution is the best. My code:
LEN(@SearchVal) - LEN(REPLACE(@SearchVal,'.',''))
is the fastest way I know to count individual characters but it's not necessary here as we're only trying to ensure that there are not two dots which is more efficiently handled with your code:
@SearchVal NOT LIKE '%.%.%'
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001