CODEWITHSUNDEEP
sql-servert-sql
Stefan Wolf
Stefan Wolf

Reputation: 45

MSSQL Check if column is all digits or empty

I have a varchar field in a table. I have to check which rows contain anything but digits (0-9). Any other character (. , etc) are not allowed.

ISNUMERIC isn't helpful because the string can get longer than anything which would be convertible to a numeric datatype in SQL-Server.

Any ideas on how to do this?

Thanks a lot!

Upvotes: 1

Views: 2658

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

You could try PATINDEX to find and reject values with non-digit characters :

declare @t table (field varchar(40))
insert into @t (field)
values
('40340,033680998078'),
('40340'),
('033680998078')

select *
from @t
where patindex('%[^0-9]%',field)=0

This query can't take advantage of any indexes and will have to scan the entire table. It's best to avoid storing non-digit characters in that field in the first place.

In general, if a field contains numeric data it should use a numeric type, not varchar. It should be an int, bigint or numeric(...,0).

On the other hand that field may be a business field storing eg an invoice number, airline ticket or VAT number.

In that case one would want to use a CHECK constraint to ensure invalid values can't be inserted in the table. This is possible using PATINDEX :

declare @t table 
( 
    field varchar(40) CHECK (patindex('%[^0-9]%',field)=0)
);

This check constraint will allow :

insert into @t (field)
values
('40340'),
('033680998078')

But reject :

insert into @t (field)
values
('40340,033680998078')

Upvotes: 0

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Suppose that "empty" means NULL:

Field NOT LIKE '%[^0-9]%'
    OR Field IS NULL

Example to get values which contain only digits or are empty:

declare @test table (Field varchar(32))

INSERT @test
VALUES
    (NULL),
    ('121414'),
    ('88977665'),
    ('234234f'),
    ('347238748d9')

select * from @test
where Field not like '%[^0-9]%'
    OR Field is null

Field

NULL

121414

88977665

Example to get values which contain nondigit characters and not empty:

declare @test table (Field varchar(32))

INSERT @test
VALUES
    (NULL),
    ('121414'),
    ('88977665'),
    ('234234f'),
    ('347238748d9')

select * from @test
where Field like '%[^0-9]%'
    AND Field is NOT null

Field

234234f

347238748d

Upvotes: 2

Related Questions