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

Question

I work on SQL server 2012 I have issue I can't update status to Numbers only when whole field have digits only from 0 to 9 . but if it have only one character or precision then it will be not valid .

1222 valid

223g not valid

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 :

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

In all of your research and attempts did you stumble across anything close to `like '%[^0-9]%'`? Perhaps with `!= ''`? And `is not NULL`? Or is it that you can't find `update`? — HABO, Aug 14 '20 at 20:21

SteveC · Accepted Answer · 2020-08-14T20:35:28.127

2

Something like this

update #acceptnumbersOnly
set
  [Status]=iif(KeyValue LIKE '%[^0-9]%', 'Not Valid Numbers Only', 'Numbers only');

Results

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

edited Aug 14 '20 at 20:35

answered Aug 14 '20 at 20:25

SteveC

5,955
2
11
24

How does your answer handle "but if it have only one character or precision then it will be not valid"? What does it mean? – HABO Aug 14 '20 at 20:28
The statement says "if there is any character other than 0, 1, 2, ..., 9 in the KeyValue column, then the Status is set to 'Not Valid Numbers Only'. The string '45.5' is evaluated as TRUE because it contains a dot '.' – SteveC Aug 14 '20 at 20:33
Doesn't the Requirements Document section "but if it have only one character or precision then it will be not valid" mean that `'½'` and `'7'` "will be not valid" because "if it have only one character"? Your statement will treat `'7'` as valid. I still don't know what the invalid "precision" looks like, or whether it is qualified by "one". (It takes extra work to help a [Help Vampire](https://web.archive.org/web/20180216144029/https://www.skidmore.edu/~pdwyer/e/eoc/help_vampire.htm).) – HABO Aug 14 '20 at 20:38
It produces the outcome requested, no? Perhaps the Requirements Document section means "'½' and '7'" (together, i.e. '7½') would not be valid. If so then the code demonstrates the requirement I think – SteveC Aug 14 '20 at 20:50

ASH · Answer 2 · 2020-08-15T13:06:17.717

1

I grew up with the Case...When logic. This works for me.

SELECT KeyValue, 
CASE
    WHEN KeyValue LIKE '%[^0-9]%' THEN 'Not Valid Numbers Only'
    ELSE 'Numbers only'
END AS Status
FROM #acceptnumbersOnly

I always thought IIF was for MS Access. I just found out about the IIF function right now. Apparently IIF was introduced in SQL Server 2012+. I would think the Case...When is a better solution, as it is portable across all versions of SQL Server. MAybe it's a moot point, because there probably are not too many people out there using pre-2012 SQL Server.

edited Aug 15 '20 at 13:06

answered Aug 14 '20 at 23:30

ASH

20,759
19
87
200

It's +1 from me for offering the choice. Yes, IIF is stigmatized by its earlier availability in Access. For me it's a little more formal because it requires the ELSE condition be provided. In a numeric non nullable column if someone leaves off the ELSE 0 in case logic it could cause an issue at some point. It's functionally equivalent so whatever people are most comfortable with that's best for them. – SteveC Aug 15 '20 at 00:54

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

2 Answers2