数字のみのレコード抽出

• MySQL

(8.0.22)
https://qiita.com/bezeklik/items/d8d74deece96cb446998

drop table tab1;
create table tab1(col1 varchar(100));
insert into tab1 values('0001');
insert into tab1 values('A001');
insert into tab1 values('00A1');
insert into tab1 values('001A');
insert into tab1 values('AAAA');
insert into tab1 values('1234');
select * from tab1;

select col1 from tab1 where regexp_like(col1, '^[0-9]+$');
select col1 from tab1 where not(regexp_like(col1, '[^0-9]'));

• Oracle

(19c)
https://gonsuke777.hatenablog.com/entry/20120215/1329301835

drop table tab1 purge;
create table tab1(col1 varchar2(100));
insert into tab1 values('0001');
insert into tab1 values('A001');
insert into tab1 values('00A1');
insert into tab1 values('001A');
insert into tab1 values('AAAA');
insert into tab1 values('1234');
commit;
select * from tab1;

select col1 from tab1 where regexp_like(col1, '^[0-9]+$');
select col1 from tab1 where not(regexp_like(col1, '[^0-9]'));

• PostgreSQL

(13)
http://blog.livedoor.jp/ogahiro_com/archives/51572673.html

drop table tab1;
create table tab1(col1 varchar(100));
insert into tab1 values('0001');
insert into tab1 values('A001');
insert into tab1 values('00A1');
insert into tab1 values('001A');
insert into tab1 values('AAAA');
insert into tab1 values('1234');
select * from tab1;

select col1 from tab1 where col1 ~ '^[0-9]+$';
select col1 from tab1 where col1 !~ '[^0-9]';

• SQL Server

(2019)
https://memorandom-nishi.hatenablog.jp/entry/2017/03/09/013222

drop table tab1;
create table tab1(col1 varchar(100));
insert into tab1 values('0001');
insert into tab1 values('A001');
insert into tab1 values('00A1');
insert into tab1 values('001A');
insert into tab1 values('AAAA');
insert into tab1 values('1234');
select * from tab1;

select col1 from tab1 where col1 not like '%[^0-9]%'