SQL经典案例之不同数据库识别可视为数字的字符串

原创 于 2025-12-16 09:12:26 发布 · 445 阅读 · 10 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #sql

2025博客之星年度评选已开启 10w+人浏览 1.1k人参与

SQL经典案例之识别可视为数字的字符串

问题描述

将字符串中的数字提取出来并返回为结果

--提取前-- >>>> ---提取后---
OLD_MIXED       NEW_MIXED
--------- >>>> ----------
ORACLE	
520               520
521               521
13DB14BD         1314
2DB2               22
3M3D               33
ZH11AN31         1131
DBA10ABD           10
MYSQL
POSTGRESQL
--------- >>>> ----------

构造测试数据

drop table t_num;
create table t_num(tid int,tmixed varchar(10));
insert into t_num values(1,'ORACLE');
insert into t_num values(2,'520');
insert into t_num values(3,'521');
insert into t_num values(4,'13DB14BD');
insert into t_num values(5,'2DB2');
insert into t_num values(6,'3M3D');
insert into t_num values(7,'ZH11AN31');
insert into t_num values(8,'DBA10ABD');
insert into t_num values(9,'MYSQL');
insert into t_num values(10,'POSTGRESQL');

-- tid的个数需要 >= tname列的数据最大长度
drop table t_10;
create table t_10(tid int);
insert into t_10 values(1);
insert into t_10 values(2);
insert into t_10 values(3);
insert into t_10 values(4);
insert into t_10 values(5);
insert into t_10 values(6);
insert into t_10 values(7);
insert into t_10 values(8);
insert into t_10 values(9);
insert into t_10 values(10);

commit;

函数认识

translate

字符转换函数

TRANSLATE(x,from_string,to_string)函数在x中查找from_string中的字符，并将其转换成to_string中对应的字符

-- Oracle
select translate('In13doing14we55learn ','0123456789','##########') from dual;
-- postgresql
select translate('In13doing14we55learn ','0123456789','##########');

image.png

replace

字符替换函数

  • PostgreSQL的replace替换函数需要3个入参
-- Oracle，默认替换成空字符串 ''
select replace('In##doing##we##learn ','#') from dual;
-- postgresql 等价写法
select replace('In##doing##we##learn ','#','');
-- postgresql/Oracle
select replace('In##doing##we##learn ','#',' ') as col1,
replace('In##doing##we##learn ','#','*') as col2,
replace('In##doing##we##learn ','##',' ') as col3;

select replace(translate('In13doing14we55learn ','0123456789','##########'),'##',' ');

image.png

rpad

往源字符串的左/右侧填充字符

rpad( string1, padded_length, [ pad_string ] )
  • string1：源字符串
  • padded_length:最终返回的字符串长度，若最终返回的字符串的长度比源字符串的小，那么此函数实际上对源字符串进行截断处理
  • pad_string:用于填充的字符，循环填充，不填默认为空字符
select rpad('newday',3),rpad('newday',3,'G'),rpad('newday',8,'G'),rpad('newday',11,'GO');

image.png

lpad 则是往左侧填充，用法等同

instr

字符位置查找函数

-- 格式一：instr(源字符串, 目标字符串)
instr( string1, string2 )

-- 格式二：instr(源字符串, 目标字符串)
instr( string1, string2 [, start_position [, nth_appearance ] ] )

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

20:13:15 pg14@postgres=# \df instr
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------
Schema              | public
Name                | instr
Result data type    | integer
Argument data types | string character varying, string_to_search character varying, beg_index integer, occur_index integer
Type                | func

-- 默认第一次出现“l”的位置；第一次出现“lo”时第一个字母“l”出现的位置；匹配不到返回0
select instr('helloworld','l'),instr('helloworld','lo'),instr('helloworld','wd') from dual;

-- 第4(l)号位置开始查找第2次出现的“l”的位置；倒数第1(d)号位置开始往回查找第一次出现的“l”的位置
select instr('helloworld','l',4,2),instr('helloworld','l',-1,1) from dual;

image.png

strpos

在PostgreSQL数据库没有instr函数，可以用strpos函数替代

11:44:21 muser@testdb=> \df strpos
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type 
------------+--------+------------------+---------------------+------
 pg_catalog | strpos | integer          | text, text          | func
(1 row)

select strpos('helloworld','l'),strpos('helloworld','lo'),strpos('helloworld','wd');

image.png

也可以通过自定义函数来代替instr的实现：

  -- 实现 1
  CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS
    $$ DECLARE pos integer;
  BEGIN
    pos := instr($1, $2, 1);
    RETURN pos;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;    
  
  -- 实现 2                    
  CREATE FUNCTION instr(string           varchar,
                        string_to_search varchar,
                        beg_index        integer) RETURNS integer AS
    $$        DECLARE pos integer NOT NULL DEFAULT 0;
    temp_str  varchar;
    beg       integer;
    length    integer;
    ss_length integer;
  BEGIN
    IF beg_index > 0 THEN
      temp_str := substring(string FROM beg_index);
      pos      := position(string_to_search IN temp_str);
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN pos + beg_index - 1;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          RETURN beg;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;
  
  -- 实现 3
  CREATE FUNCTION instr(string           varchar,
                        string_to_search varchar,
                        beg_index        integer,
                        occur_index      integer) RETURNS integer AS
    $$           DECLARE pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str     varchar;
    beg          integer;
    i            integer;
    length       integer;
    ss_length    integer;
  BEGIN
    IF beg_index > 0 THEN
      beg      := beg_index;
      temp_str := substring(string FROM beg_index);
      FOR i IN 1 .. occur_index LOOP
        pos := position(string_to_search IN temp_str);
        IF i = 1 THEN
          beg := beg + pos - 1;
        ELSE
          beg := beg + pos;
        END IF;
        temp_str := substring(string FROM beg + 1);
      END LOOP;
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN beg;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          occur_number := occur_number + 1;
          IF occur_number = occur_index THEN
            RETURN beg;
          END IF;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;

substr

字符串截取函数
substr(参数1，参数2[,参数3])：该函数返回被截后的子字符串

  • 参数1为要截取的字符串
  • 参数2为截取的开始位置
  • 参数3可选，表示截取长度，默认截取到末尾

pg和mysql的substr()函数的第一个位置都是为1的，pg为0的时候返回数据是有值的，但mysql为0时候返回数据为空

-- PostgreSQL
select substr('abcdef',1),substr('abcdef',8),substr('abcdef',0,3),substr('abcdef',1,3),substr('abcdef',-2),substr('abcdef',-3,2);
-- Oracle
select substr('abcdef',1),substr('abcdef',8),substr('abcdef',0,3),substr('abcdef',1,3),substr('abcdef',-2),substr('abcdef',-3,2) from dual;

image.png

  • 在 oracle 中，substr(x, 0, 3)与substr(x, 1, 3)的意思相同，都是截取第一个到第三个元素
  • 在 postgresql 中，substr(x, 0, 3)截取前两位元素，substr(x, 1, 3)截取前三位元素
  • 在 mysql 中，substr(x, 0, 3)返回空字符串’'，substr(x, 1, 3)截取前三位元素

substr参数为负数时结果不一致，由于截取方式不同，Oracle负数是从右开始计算下标，Postgre负数依旧是从左开始计算下标，可以通过长度计算获取开始截取的下标

-- PostgreSQL
SELECT substr('abcdef',-3,2),substr('abcdef', length('abcdef') - 3 + 1, 2);
-- Oracle
select substr('abcdef',-3,2) , substr('abcdef', length('abcdef') - 3 + 1, 2) from dual;

image.png

ascii

获取字符对应的ascii值

image.png

字符 ‘0’ ~ ‘9’ 对应十进制数字 48 ~ 57
字符 ‘A’ ~ ‘Z’ 对应十进制数字 65 ~ 90
字符 ‘a’ ~ ‘z’ 对应十进制数字 97 ~ 122

select ascii('0'),ascii('9'),ascii('A'),ascii('Z'),ascii('a'),ascii('z');

image.png

Oracle

select tid,tmixed as old_mixed,to_number(
	case when replace(translate(tmixed,'0123456789','9999999999'),'9') is not null
	then replace(translate(tmixed,replace(translate(tmixed,'0123456789','9999999999'),'9'),rpad('#',length(tmixed),'#')),'#')
	else tmixed
	end
	) as new_mixed
from t_num
where instr(translate(tmixed,'0123456789','9999999999'),'9') > 0;

image.png

拆分理解：

col old_mixed format a15
col col1 format a15
col col2 format a15
col col3 format a15

select tid,tmixed as old_mixed,
replace(translate(tmixed,'0123456789','9999999999'),'9') as col1,
		rpad('#',length(tmixed),'#') as col2,
replace(translate(tmixed,replace(translate(tmixed,'0123456789','9999999999'),'9'),rpad('#',length(tmixed),'#')),'#')  as col3
from t_num;

image.png

过滤条件where将数字都转换成9再获取第一次出现9的位置，以此来区分该行数据是否存在数字【 >0 】

select tid,tmixed,instr(translate(tmixed,'0123456789','9999999999'),'9') from t_num;

image.png

PostgreSQL

select tid,tmixed as old_mixed,cast(
	case when replace(translate(tmixed,'0123456789','9999999999'),'9','') is not null
	then replace(translate(tmixed,replace(translate(tmixed,'0123456789','9999999999'),'9',''),rpad('#',length(tmixed),'#')),'#','')
	else tmixed
	end as integer
	) as new_mixed
from t_num
where strpos(translate(tmixed,'0123456789','9999999999'),'9') > 0;

子查询部分同Oracle，区别在于replace函数写法需要写明替换的第三个入参，替换成空字符，过滤条件where拆分：处理函数不同，但原理等同

select 
	tid,tmixed,
	translate(tmixed,'0123456789','9999999999'),
	strpos(translate(tmixed,'0123456789','9999999999'),'9')
from t_num;

image.png

MySQL

MySQL不支持translate函数，因此需遍历每一行数据并逐字符对其进行处理，使用group_concat将数字拼接起来得到tmixed完整的数字部分，再将结果转换为数字unsigned

select tmixed as old_mixed,cast(group_concat(c order by pos separator '') as unsigned) as new_mixed
from (
	select tn.tmixed,iter.pos,substr(tn.tmixed,iter.pos,1) as c
	from t_num as tn,(select tid as pos from t_10) as iter
	where iter.pos <= length(tn.tmixed) and ascii(substr(tn.tmixed,iter.pos,1)) between 48 and 57
	) as y
group by tmixed
order by 1;

image.png

拆分认识：遍历所有字符并过滤ascii值为0～9的，即提取数字字符

select tn.tmixed,iter.pos,substr(tn.tmixed,iter.pos,1) as c
	from t_num as tn,(select tid as pos from t_10) as iter
	where iter.pos <= length(tn.tmixed) and ascii(substr(tn.tmixed,iter.pos,1)) between 48 and 57 order by 1;

image.png

9、深入探索关系型数据库与 SQL 解析
hadoop5ranger的博客
11-30 7
本文深入探讨了关系型数据库与SQL解析的核心技术，涵盖数据库基础、SQL查询语法、关系操作及SQL在程序中的使用方式。重点介绍了SQL解析过程中词法分析器的设计与实现，以及逆波兰表示法（RPN）在解析中的应用优势。文章还展示了如何将SQL转换为RPN并生成抽象语法树，结合实际案例说明SQL操作的综合运用，并提供了查询优化建议，帮助读者提升数据处理效率与数据库管理能力。
【HALCON工业视觉应用探索】05. C#+Halcon金属零件字符识别：从零搭建工业OCR系统
专注于人工智能、软件开发、工控自动化、工厂数字化及智能化等领域，希望和大家共同进步！
08-14 1122
摘要：金属零件字符识别是工业追溯与质量控制的核心技术，但面临反光、划痕、油污等现场干扰。本文基于C#与Halcon 24.11，从零构建工业级OCR系统：从十分钟搭建基础识别流程，到进阶处理激光刻印、钢印、喷墨等多类型字符，详解预处理增强、定位分割、混合识别全流程。通过顶帽变换抗反光、多光谱融合去油污、深度学习辅助疑难字符识别等技术，在公开数据集测试中，清洁场景识别率达100%，轻度油污场景98.7%，重度划痕场景89.3%，平均处理时间150ms。
SQL经典实例
weixin_39789796的博客
05-03 2066
文档通过大量实例展示了SQL在数据操作中的核心技术，从基础查询到高级聚合、字符串处理、元数据查询等，覆盖多数据库差异，强调实际应用中的最佳实践（如处理Null值、避免笛卡儿积、合理使用索引等）。每个实例结合问题、解决方案和讨论，帮助读者理解不同场景下的SQL策略，是SQL开发和优化的重要参考。
NL2SQL技术方案系列(4)：金融领域NL2SQL技术方案以及行业案例实战讲解2
丨汀、的博客
04-24 1188
NL2SQL技术方案系列(4)：金融领域NL2SQL技术方案以及行业案例实战讲解2
图数据库Neo4j详解
Aidon博客
11-10 4130
​ Neo4j是一个开源的、 无Shcema的、 基于java开发的图形数据库，它将结构化数据存储在图中而不是表中。本文覆盖图数据库和Neo4j概要、Neo4j安装部署、Neo4j实践操作、Neo4j Admin、Neo4j API开发。具体包含图数据库概念、图论起源、、节点-关系图、图数据库分类、图数据库应用场景、图数据库与关系型数据库区别、图数据库优势、Neo4j特点、优势、数据模型、Neo4j安装部署、Cypher应用、图数据库函数、图数据库操作、索引和约束管理、Java和Python API开发等。
SQL学习
m0_57024671的博客
03-12 337
sql学习的内容
分布式数据库（一）
持续Java学习中
12-08 2076
像Google Spanner和TiDB这样的Distributed SQL数据库崛起，NewSQL数据库的地位收到了进一步的挑战NewSQL更像是用户侧的狂欢，它可以解决一类问题，但并不完备，需要小心评估和使用，Distributed SQL数据库使用的是特殊的底层存储引擎，来构建水平可伸缩的数据库。一致且容忍分区的系统，更倾向于减少服务时间，而不是将不一致的数据提供出去，一些面相交易场景构建的NewSQL数据库，如TiDB、PolarDB，倾向于这种策略，会生成自己的A，即可用性很高。
SQL注入攻击链、手法、思路 入门到进阶一篇足够！
最新发布
BanDaPao的博客
11-03 760
各位安全研究员、代码设计师以及渗透测试爱好者们，今天我们讲SQL注入共同解剖SQL注入的完整攻击链。我们不会停留在简单的 ' OR '1'='1 ,而是要深入骨髓，从漏洞发现到最终拿下服务器高级权限，一步步拆解，全程高能，偏重于实战与深度漏洞挖掘。 目前博主也是在学习阶段，有讲得不对或者不够清晰的地方还望各位大佬在评论区指点！
SQL注入的“无影脚”：详解空格绕过WAF的N种方法
haishiqiguai的博客，欢迎你的到来，希望你能在此找到汝心中所求
10-01 1027
本篇文章详细讲解了在SQL注入攻击中，当空格字符被WAF（Web应用防火墙）过滤时，攻击者如何利用注释符、括号、特殊编码、数学运算符等多种技巧替代空格，绕过防御并成功执行恶意SQL语句，同时提供了实战案例和相应的防御建议
Java高手的30k之路｜面试宝典｜精通数据库
weixin_41379244的博客
07-01 753
ACID ｜ full join | cross join | group by & having | 窗口函数 ｜数据库设计规范化和反规范化｜缓存｜分区｜分库分表｜事务｜分布式事务｜两阶段提交
Mysql数据库经验总结
liuzhiqiang66的博客
03-14 1240
MySQL数据库开发规范及SQL语句语法与SQL优化，联合索引的使用方式与python连接操作数据库
NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL、SQL-PaLM）、新一代数据集BIRD-SQL解读
丨汀、的博客
04-15 2913
NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
SQL Server 2000中处理半个汉字的解决方案
资源摘要信息:"在SQL Server 2000中...综上所述，该知识点涵盖了字符串处理、字节级操作、编码原理、用户自定义函数编写、数据完整性修复等多个维度，是SQL Server数据库开发中关于文本数据质量控制的经典案例之一。
SQL Server 的完整下载安装教程
热门推荐
SUOYUEのBLOG
11-30 1万+
SQL Server + SSM 的完整下载安装教程
数据库的完整性
SUOYUEのBLOG
10-21 3629
数据库的完整性
时序数据库 TDengine 的入门体验和操作记录
SUOYUEのBLOG
09-15 2921
时序数据库 TDengine 的学习和使用经验
关系数据理论
SUOYUEのBLOG
11-06 1762
关系数据理论
关系数据库标准语言SQL
SUOYUEのBLOG
09-26 1314
关系数据库 SQL
执行计划 Seq Scan 真相：为何它不一定需要全表扫描 ？
SUOYUEのBLOG
06-17 1032
PostgreSQL数据库执行计划 Seq Scan 的隐秘优化场景
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值