SQL地址门牌排序，字典序转为数字序

页面有一批地址数据查询，结果字符排序默认是字典序的，所以造成了门牌3号在30号之前，影响用户体验；

id, road_code, road_name, address_fullname, address_name
102	10086	人民一路	北江省南海市西湖区人民一路3号	3号
103	10086	人民一路	北江省南海市西湖区人民一路11号	11号
109	10086	人民一路	北江省南海市西湖区人民一路27号	27号
116	10086	人民一路	北江省南海市西湖区人民一路7号	7号
108	10086	人民一路	北江省南海市西湖区人民一路30号	30号
114	122847	幸福大道	北江省幸福市中新区幸福大道7号	7号

SELECT id, road_code, road_name, address_fullname, address_name
FROM address
WHERE road_code = 10086 
ORDER BY address_name ASC

在这里插入图片描述
想到办法是提取名称的数字再排序。数据量大有两千多万条，不可能都跑一边提取排序，要么冗余一个字段存放提取的数字？

“如非必要，勿增实体”——奥卡姆剃刀原则

新加字段代码要改，治理维护也是问题。考虑到就这里功能用到了这个排序，而且道路代码是必传值，先被road_code值筛选过滤后，最后实际提取转换的数据并不多。

SELECT id, road_code, road_name, address_fullname, address_name
FROM address
WHERE road_code = 10086 
ORDER BY COALESCE(NULLIF(regexp_replace(address_name, '\D','','g'), ''), '0')::NUMERIC ASC

COALESCE(NULLIF(regexp_replace(address_name, ‘\D’,‘’,‘g’), ‘’), ‘0’)::NUMERIC
正则全局匹配将地址中非数字替换成空字符，为了防止地址不存在或者没有数字的情况，
使用NULLIF()和COALESCE()兜底，统一置为’0’，最后转为NUMERIC数字类型用作排序。
（空字符串 ‘’::NUMERIC 类型转换报异常）
在这里插入图片描述

注：
COALESCE(value [, …])
返回第一个非空参数的值。当且仅当所有参数都为空时才会返回NULL空值。

NULLIF(value1, value2)
当value1和value2相等时，NULLIF返回NULL空值。否则它返回value1。

另附，测试表和数据：

DROP TABLE IF EXISTS "public"."address";
CREATE TABLE "public"."address" (
  "id" int8 NOT NULL,
  "road_code" int8,
  "road_name" varchar(255) COLLATE "pg_catalog"."default",
  "address_fullname" varchar(255) COLLATE "pg_catalog"."default",
  "address_name" varchar(255) COLLATE "pg_catalog"."default"
);

COMMENT ON COLUMN "public"."address"."id" IS '主键';
COMMENT ON COLUMN "public"."address"."road_code" IS '道路编码';
COMMENT ON COLUMN "public"."address"."road_name" IS '道路名称';
COMMENT ON COLUMN "public"."address"."address_fullname" IS '地址全名';
COMMENT ON COLUMN "public"."address"."address_name" IS '地址名称';

INSERT INTO "public"."address" VALUES (102, 10086, '人民一路', '北江省南海市西湖区人民一路3号', '3号');
INSERT INTO "public"."address" VALUES (103, 10086, '人民一路', '北江省南海市西湖区人民一路11号', '11号');
INSERT INTO "public"."address" VALUES (109, 10086, '人民一路', '北江省南海市西湖区人民一路27号', '27号');
INSERT INTO "public"."address" VALUES (116, 10086, '人民一路', '北江省南海市西湖区人民一路7号', '7号');
INSERT INTO "public"."address" VALUES (108, 10086, '人民一路', '北江省南海市西湖区人民一路30号', '30号');
INSERT INTO "public"."address" VALUES (114, 122847, '幸福大道', '北江省幸福市中新区幸福大道7号', '7号');

ALTER TABLE "public"."address" ADD CONSTRAINT "address_pkey" PRIMARY KEY ("id");