在SQL里MAC地址格式转换

Question

MAC地址格式转换
现在有三种MAC格式：00:09:5B:EC:EE:F2/00-01-6C-06-A6-29/00016C06A629
这三种格式我需要全部转换成第一种带冒号的，请问最后一中没有符号的怎么转换，目前我只做到了转换第二种，使用replace,2代表的是MAC地址

```sql
case when {?ADDRESS_TYPE?} = '2' and instr({?ADDRESS?},'-')>0 then replace({?ADDRESS?},'-',':') else {?ADDRESS?} end

```

Answer 1

two choice：

DECLARE @address_type INT = 3
DECLARE @address VARCHAR(20) = '00016C06A629'
SELECT
    CASE
        WHEN @address_type = 2 AND CHARINDEX('-', @address) > 0 THEN REPLACE(@address, '-', ':')
        WHEN @address_type = 3 THEN SUBSTRING(@address, 1, 2) + ':' + SUBSTRING(@address, 3, 2) + ':' + SUBSTRING(@address, 5, 2) + ':' + SUBSTRING(@address, 7, 2) + ':' + SUBSTRING(@address, 9, 2) + ':' + SUBSTRING(@address, 11, 2)
        ELSE @address
    END AS formatted_address


DECLARE @address_type INT = 3
DECLARE @address VARCHAR(20) = '00016C06A629'

SELECT
    STUFF(STUFF(STUFF(STUFF(STUFF(@address, 3, 0, ':'), 6, 0, ':'), 9, 0, ':'), 12, 0, ':'), 15, 0, ':') AS formatted_address