February 28, 2018 at 12:22 pm
Good Day!
I am looking for a way to test a string to make sure it only has numbers and/or a decimal point ( Not more than one decimal point )
So what I am really looking for is a way to test for decimal number ( and integers )
Any idea ?
declare @SearchVal varchar(10) = '1234.00'
select 'yes'
where
@SearchVal like '%[0-9][.][0-9]%'
February 28, 2018 at 12:27 pm
WHERE @SearchVal NOT LIKE '%[^0-9.]%'
AND LEN(@SearchVal) - LEN(REPLACE(@SearchVal,'.','')) < 2
-- Itzik Ben-Gan 2001
February 28, 2018 at 12:28 pm
declare @SearchVal varchar(10) = '1234.00'
select 'yes'
where @SearchVal NOT LIKE '%[^0-9.]%'
AND @SearchVal NOT LIKE '%.%.%'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 12:33 pm
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
February 28, 2018 at 12:42 pm
ZZartin - Wednesday, February 28, 2018 12:33 PMAnother 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), $)
-- Itzik Ben-Gan 2001
February 28, 2018 at 12:52 pm
Alan.B - Wednesday, February 28, 2018 12:42 PMZZartin - Wednesday, February 28, 2018 12:33 PMAnother 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 NULLGood 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 1:09 pm
drew.allen - Wednesday, February 28, 2018 12:52 PMAlan.B - Wednesday, February 28, 2018 12:42 PMZZartin - Wednesday, February 28, 2018 12:33 PMAnother 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 NULLGood 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 '%.%.%'
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply