根据顺序和模式对列进行分类

Question

我在PostgreSQL表ABC中有一列Id。

Id是长度为10的bigint(minLength=maxLength=10)

我必须编写一个SQL查询，并将Id分类为：

• 任何以恰好3个连续奇数开始并以至少2个连续偶数结束的Id都被归类为"A类">
• 第二个数字大于6或第三个字符小于或等于4的任何Id都被归类为"类型B">
• 任何奇数Id都被归类为"C类"，除非它以7结尾
• 任何偶数Id都被归类为"TYPE D"，除非它以2结尾
• 所有其他Id都被归类为"类型X">

Answer 1

每个类型都可以用正则表达式找到。

create table ABC (
Id decimal(10) primary key check(length(Id::text)=10)
);
insert into ABC (Id) values
(1350000042)
, (1640000000)
, (1090000503)
, (1294567890)
, (1090000907)
, (1090000902)

/*
Type A: Any Id that commences with exactly 3 consecutive odd digits
and concludes with at least 2 consecutive even digits;
Type B: Any Id where the second digit is greater than 6 
or the third character is less than or equal to 4;
Type C: Any odd-numbered Id unless it ends in 7;
Type D: Any even-numbered Id unless it ends in 2;
Type X: All other Id;
*/
select Id
, case
when Id::text ~ '^[13579]{3}[02468].*[02468]{2}$' then 'A'
when Id::text ~ '^(.[7-9]|..[0-4])' then 'B'
when Id::text ~ '[1359]$' then 'C'
when Id::text ~ '[0468]$' then 'D'
else 'X'
end as Type
from ABC
order by Type

id|type---------：|：---1350000042|A1760000000|B16.4亿|B1090000503|C1294567890 |天1090000907|X1090000902|X

db<gt；小提琴这里