Oracle-这就是SQL - SPLIT MAC地址

最新推荐文章于 2022-07-29 10:22:38 发布

海点

最新推荐文章于 2022-07-29 10:22:38 发布

阅读量1k

点赞数 1

分类专栏： Oracle

本文链接：https://blog.csdn.net/cloud952788/article/details/38234321

版权

Oracle 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1.2*6=12字符MAC地址加上冒号

原字符串中没有冒号：'8c0ee3355d67'

listagg列转行函数

SQL> select listagg(substr('8c0ee3355d67', 1 + (level - 1) * 2, 2), ':') within group(order by rownum) path
  2    from dual
  3  connect by level <= ceil(length('8c0ee3355d67') / 2)
  4  ;
 
PATH
--------------------------------------------------------------------------------
8c:0e:e3:35:5d:67

正则表达式替换函数

SQL> select rtrim(regexp_replace('8c0ee3355d67', '(\w{2})', '\1:'), ':') as "1st",
  2         rtrim(regexp_replace('8c0ee3355d67', '(..)', '\1:'), ':') as "2nd"
  3    from dual;
 
1st               2nd
----------------- -----------------
8c:0e:e3:35:5d:67 8c:0e:e3:35:5d:67

MS SQL的方法
GO
with ord as ( SELECT 1 level UNION ALL
SELECT level + 1 FROM ord WHERE ord.level <len('a1b2c3d4e5f6')/2
)
SELECT SUBSTRING('a1b2c3d4e5f6',2*(level-1)+1,2) + ':' FROM ord for xml path('');