SQLTeam.com | Weblogs | Forums

How to update status to wrong numeric if at least on character exist?

I work on SQL server 2012 I have keyValue field I need when user write on it any character except 0 to 9

then update status to Not valid number only

and

if field key value have all numbers only then update status to numbers only

create table #acceptnumbersOnly
(
KeyValue nvarchar(50),
Status nvarchar(50)
)
insert into #acceptnumbersOnly(KeyValue)
values 
('233'),
('g25k'),
('25k'),
('gkg'),
('145'),
('45.5')

Expected Result will be :slight_smile:

KeyValue Status
233 Numbers only
g25k Not Valid Numbers Only
25k Not Valid Numbers Only
gkg Not Valid Numbers Only
145 Numbers only
45.5 Not Valid Numbers Only
SELECT KeyValue,
    CASE WHEN KeyValue LIKE '%[^0-9]%' THEN 'Not Valid Numbers Only' 
         ELSE 'Numbers Only' END AS Status
FROM #acceptnumbersOnly
--ORDER BY KeyValue
1 Like

thanks it solved