SQL基础操作之字符串处理操作教程

最新推荐文章于 2024-08-10 23:17:37 发布

milijiangjun

最新推荐文章于 2024-08-10 23:17:37 发布

阅读量634

点赞数

分类专栏： Sql

本文链接：https://blog.csdn.net/milijiangjun/article/details/103453774

版权

本文深入讲解SQL中的字符串处理操作，包括连接、截取、查找、替换等常见功能，帮助读者掌握在数据库中对字符串进行高效管理的技巧。

摘要由CSDN通过智能技术生成

7.6.1 生成自增值
需求：

通过SQL生成一个1到1000条记录.

解决方法:

通过CTE的递归来实现该需求.

SQLServer:

?
1
2
3
4
5
6
7
8
9
10
11
DECLARE @startINT, @endINT
SELECT @start=1, @end=1000
;WITH NumberSequence( Number) AS
(
    SELECT @start ASNumber
       UNION ALL
    SELECT Number + 1
       FROM NumberSequence
       WHERE Number <@end
)
SELECT * FROM NumberSequence OPTION(MaxRecursion 1000)
执行结构:

Number

1

2

3

4

5

6

7

8

9

10

…

Oracle:

?
1
2
3
4
5
6
7
WITH t(num)AS (
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT t.num+1
  FROM t WHERE t.num<100
  )
SELECT * FROM t;
执行结果:

Num

1

2

3

4

5

6

7

8

9

10

…

Mysql(8.0及以上版本):

?
1
2
3
4
5
6
7
WITH RECURSIVE cte (num) AS
(
  SELECT 1
  UNION ALL
  SELECT num+1 FROM cte WHERE num <100
)
SELECT * FROM cte;
执行结果:

Num

1

2

3

4

5

6

7

8

9

10

…

7.6.2 遍历字符串里的每个值
需求：

打印出ename为’King’的名字里每一个字母,每个字母占一行.

解决方法:

通过自增表和emp表先cross join(笛卡尔积),然后再通过ename的len(ename的长度)进行过滤,最终得到显示每个字母的结果.

SQLServer:

?
1
2
3
4
SELECT SUBSTRING (e.ENAME,seq.pos,1) AS ename_Split
FROM (SELECT ENAME FROM emp WHERE ename= 'KING' ) e,
(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq
WHERE seq.pos <= LEN(e.ename)
执行结果:

ename_Split

K

I

N

G

注:

1: 这里master.[dbo].[spt_values]是一张特殊的系统视图,里面存了从0到2047总2048条自增序列.

2: 如果不明白,可以分段来看.

Step1:

?
1
2
3
SELECT e.*,seq.* FROM
(SELECT ENAME FROM emp WHERE ename= 'KING' ) e,
(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P' AND number>0)seq
执行结果:

ENAME

pos

KING

1

KING

2

KING

3

KING

4

KING

5

KING

6

KING

7

KING

8

…

…

Sept2:

?
1
2
3
SELECT SUBSTRING(e.ENAME,seq.pos,1)AS ename_Split
FROM .. e, ..seq
WHERE seq.pos<=LEN(e.ename)
这里通过SUBSTRNG函数,每次的开始位置不断调整,每次仅取一个字符,再通过LEN函数过滤.所以得到最终结果.如果不熟悉SUBSTRING的语法,这里简单介绍下.

SUBSTRING ( expression, start, length )

1) 参数expression是要截取的原始字符串,比如这里的” KING”

2) 参数start是要截取的位置,比如从第2个位置开始,那应该从” I”往后数.

3) 参数length是要截取的长度,沿用上一行的例子,如果长度定义为2,则最终截取字符串是”IN”

Oracle:

?
1
2
3
4
5
6
7
8
9
10
WITH t(num) AS (
  SELECT 1 FROM DUAL
  UNION ALL
  SELECT t.num+1
  FROM t WHEREt.num<100
  )
SELECT SUBSTR(e.ENAME,seq.num,1) AS ename_Split FROM
(SELECT ENAME FROM emp WHERE ename ='KING' ) e,
(SELECT num FROM t)seq
WHERE seq.num <= LENGTH(e.ename)
Mysql8.0:

?
1
2
3
4
5
6
7
8
9
10
WITH RECURSIVE cte (num)AS
(
  SELECT 1
  UNION ALL
  SELECT num+1 FROM cte WHERE num <100
)
SELECT SUBSTRING(e.ENAME,seq.num,1) AS ename_Split FROM
(SELECT ENAME FROM emp WHERE ename ='KING' ) e,
(SELECT num FROM cte)seq
WHERE seq.num <= LENGTH(e.ename)
7.6.3 处理含引号的字符串
需求：

往dept表里插入dname为Test’s,loc为Beijing,deptno为100的数据.

解决方法:

这里有位引号是特殊符号,所以需要特殊处理,比如如果双引号包裹起来.

Mysql:

?
1
INSERT INTO dept VALUES(100,'Test\'s','Beijing');
Sql Server:

?
1
2
3
4
5
6
7
8
BEGIN TRAN
   SET IDENTITY_INSERTdeptON;
   GO
   INSERT INTO dept(deptno,dname,loc) VALUES (100,'Test''s','Beijing');
   SELECT * FROM dept WHERE deptno=100;
   SET IDENTITY_INSERTdeptOFF;
   GO
ROLLBACK TRAN
执行结果:

deptno

dname

loc

100

Test's

Beijing

7.6.4 计算某个字符出现的次数
需求：

查询emp表emptno是7499的用户的job里S出现的次数.

解决方法:

这里length(len)结合replace函数算出字符串出现的次数.

Sql Server:

?
1
2
SELECT empno,job,(LEN(JOB) - LEN(REPLACE(JOB,'S','')))/LEN('S') AS StrFreq
FROM emp WHERE empno=7499;
empno

job

StrFreq

7499

SALESMAN

2

Mysql:

?
1
2
SELECT empno,job,ROUND((LENGTH(JOB) - LENGTH(REPLACE(JOB,'S','')))/LENGTH('S')) AS StrFreq
FROM emp WHERE empno=7499;
注:这里除以LENGTH('S')是为了考虑传入的字符串是2位以及以上的情况,比如’SS’