number validation with like operator in ms sql server

eh57zj3b  于 2023-05-16  发布在  SQL Server
关注(0)|答案(1)|浏览(123)

I have a table with values('99','-99','99.99','-99.99','99-','$99','99#','99.99.99','-99.99.99' ,'-99-99.99'); I need output like this(99,-99,99.99,-99.99);

I tried with like operator but facing problem.

atmip9wb

atmip9wb1#

Not sure that I understood your logic correctly but looks like you need to get only numbers. If yes, you can use isNumeric or Try_Parse for checking your string values.

declare @table table (strr varchar(50))

insert into @table
values
('99'),
('-99'),
('99.99'),
('-99.99'),
('99-'),
('$99'),
('99#'),
('99.99.99'),
('-99.99.99'),
('-99-99.99')

--not like '%[^0-9.-]%' is a double negative to avoid $99 because it's a number
select strr from @Table where isnumeric(strr) = 1 and strr not like '%[^0-9.-]%'

--data type depends on your expectations. I chose float
select strr from @Table where try_parse(strr as float using 'en-US') is not null

Both variants return the same result set:

相关问题