? ? ? ? ? ? ? ? ? ? ? ? ? ?遍歷字符串里的每個值

需求描述

需求：打印出ename為’King’的名字里每一個字母,每個字母占一行.

解決方法:通過自增表和emp表先cross join(笛卡爾積),然后再通過ename的len(ename的長度)進行過濾,最終得到顯示每個字母的結果.

注: 數據庫數據集SQL腳本詳見如下鏈接地址

員工表結構和數據初始化SQL腳本

SQL代碼

--SQL Server: 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)

執行結果

--Oracle: WITH t(num) AS (SELECT 1 FROM DUALUNION ALLSELECT t.num+1FROM t WHERE t.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) --Mysql 8.0: WITH RECURSIVE cte (num) AS (SELECT 1UNION ALLSELECT 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)

?

總結

以上是生活随笔為你收集整理的处理字符串_2_遍历字符串里的每个值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。