Skip to content

Instantly share code, notes, and snippets.

@kthy

kthy/mac_to_label.sql

Created February 1, 2018 07:10
Show Gist options
  • Save kthy/a8a5af307cecee0f0a061f8ecd8f6a29 to your computer and use it in GitHub Desktop.
Save kthy/a8a5af307cecee0f0a061f8ecd8f6a29 to your computer and use it in GitHub Desktop.
Download ZIP
Converts a MAC address to a character string
Raw
mac_to_label.sql
CREATE OR ALTER FUNCTION dbo.CharLookup (@C AS NVARCHAR(max))
RETURNS NVARCHAR(max)
BEGIN
RETURN CASE
WHEN @C = '00' THEN NCHAR(0x0030)
WHEN @C = '01' THEN NCHAR(0x0031)
WHEN @C = '02' THEN NCHAR(0x0032)
WHEN @C = '03' THEN NCHAR(0x0033)
WHEN @C = '04' THEN NCHAR(0x0034)
WHEN @C = '05' THEN NCHAR(0x0035)
WHEN @C = '06' THEN NCHAR(0x0036)
WHEN @C = '07' THEN NCHAR(0x0037)
WHEN @C = '08' THEN NCHAR(0x0038)
WHEN @C = '09' THEN NCHAR(0x0039)
WHEN @C = '0a' THEN NCHAR(0x0040)
WHEN @C = '0b' THEN NCHAR(0x0041)
WHEN @C = '0c' THEN NCHAR(0x0042)
WHEN @C = '0d' THEN NCHAR(0x0043)
WHEN @C = '0e' THEN NCHAR(0x0044)
WHEN @C = '0f' THEN NCHAR(0x0045)
WHEN @C = '10' THEN NCHAR(0x0046)
WHEN @C = '11' THEN NCHAR(0x0047)
WHEN @C = '12' THEN NCHAR(0x0048)
WHEN @C = '13' THEN NCHAR(0x0049)
WHEN @C = '14' THEN NCHAR(0x004a)
WHEN @C = '15' THEN NCHAR(0x004b)
WHEN @C = '16' THEN NCHAR(0x004c)
WHEN @C = '17' THEN NCHAR(0x004d)
WHEN @C = '18' THEN NCHAR(0x004e)
WHEN @C = '19' THEN NCHAR(0x004f)
WHEN @C = '1a' THEN NCHAR(0x0050)
WHEN @C = '1b' THEN NCHAR(0x0051)
WHEN @C = '1c' THEN NCHAR(0x0052)
WHEN @C = '1d' THEN NCHAR(0x0053)
WHEN @C = '1e' THEN NCHAR(0x0054)
WHEN @C = '1f' THEN NCHAR(0x0055)
WHEN @C = '20' THEN NCHAR(0x0056)
WHEN @C = '21' THEN NCHAR(0x0057)
WHEN @C = '22' THEN NCHAR(0x0058)
WHEN @C = '23' THEN NCHAR(0x0059)
WHEN @C = '24' THEN NCHAR(0x005a)
WHEN @C = '25' THEN NCHAR(0x0061)
WHEN @C = '26' THEN NCHAR(0x0062)
WHEN @C = '27' THEN NCHAR(0x0063)
WHEN @C = '28' THEN NCHAR(0x0064)
WHEN @C = '29' THEN NCHAR(0x0065)
WHEN @C = '2a' THEN NCHAR(0x0066)
WHEN @C = '2b' THEN NCHAR(0x0067)
WHEN @C = '2c' THEN NCHAR(0x0068)
WHEN @C = '2d' THEN NCHAR(0x0069)
WHEN @C = '2e' THEN NCHAR(0x006a)
WHEN @C = '2f' THEN NCHAR(0x006b)
WHEN @C = '30' THEN NCHAR(0x006c)
WHEN @C = '31' THEN NCHAR(0x006d)
WHEN @C = '32' THEN NCHAR(0x006e)
WHEN @C = '33' THEN NCHAR(0x006f)
WHEN @C = '34' THEN NCHAR(0x0070)
WHEN @C = '35' THEN NCHAR(0x0071)
WHEN @C = '36' THEN NCHAR(0x0072)
WHEN @C = '37' THEN NCHAR(0x0073)
WHEN @C = '38' THEN NCHAR(0x0074)
WHEN @C = '39' THEN NCHAR(0x0075)
WHEN @C = '3a' THEN NCHAR(0x0076)
WHEN @C = '3b' THEN NCHAR(0x0077)
WHEN @C = '3c' THEN NCHAR(0x0078)
WHEN @C = '3d' THEN NCHAR(0x0079)
WHEN @C = '3e' THEN NCHAR(0x007a)
WHEN @C = '3f' THEN NCHAR(0x00c1)
WHEN @C = '40' THEN NCHAR(0x00c4)
WHEN @C = '41' THEN NCHAR(0x00c5)
WHEN @C = '42' THEN NCHAR(0x00c6)
WHEN @C = '43' THEN NCHAR(0x00c9)
WHEN @C = '44' THEN NCHAR(0x00cf)
WHEN @C = '45' THEN NCHAR(0x00d0)
WHEN @C = '46' THEN NCHAR(0x00d1)
WHEN @C = '47' THEN NCHAR(0x00d3)
WHEN @C = '48' THEN NCHAR(0x00d4)
WHEN @C = '49' THEN NCHAR(0x00d6)
WHEN @C = '4a' THEN NCHAR(0x00d8)
WHEN @C = '4b' THEN NCHAR(0x00da)
WHEN @C = '4c' THEN NCHAR(0x00dc)
WHEN @C = '4d' THEN NCHAR(0x00df)
WHEN @C = '4e' THEN NCHAR(0x00e1)
WHEN @C = '4f' THEN NCHAR(0x00e4)
WHEN @C = '50' THEN NCHAR(0x00e5)
WHEN @C = '51' THEN NCHAR(0x00e6)
WHEN @C = '52' THEN NCHAR(0x00e7)
WHEN @C = '53' THEN NCHAR(0x00e9)
WHEN @C = '54' THEN NCHAR(0x00ef)
WHEN @C = '55' THEN NCHAR(0x00f0)
WHEN @C = '56' THEN NCHAR(0x00f1)
WHEN @C = '57' THEN NCHAR(0x00f3)
WHEN @C = '58' THEN NCHAR(0x00f4)
WHEN @C = '59' THEN NCHAR(0x00f6)
WHEN @C = '5a' THEN NCHAR(0x00f8)
WHEN @C = '5b' THEN NCHAR(0x00fa)
WHEN @C = '5c' THEN NCHAR(0x00fc)
WHEN @C = '5d' THEN NCHAR(0x0259)
WHEN @C = '5e' THEN NCHAR(0x0283)
WHEN @C = '5f' THEN NCHAR(0x0292)
WHEN @C = '60' THEN NCHAR(0x0393)
WHEN @C = '61' THEN NCHAR(0x0394)
WHEN @C = '62' THEN NCHAR(0x0398)
WHEN @C = '63' THEN NCHAR(0x039b)
WHEN @C = '64' THEN NCHAR(0x039e)
WHEN @C = '65' THEN NCHAR(0x03a0)
WHEN @C = '66' THEN NCHAR(0x03a3)
WHEN @C = '67' THEN NCHAR(0x03a6)
WHEN @C = '68' THEN NCHAR(0x03a8)
WHEN @C = '69' THEN NCHAR(0x03a9)
WHEN @C = '6a' THEN NCHAR(0x03b1)
WHEN @C = '6b' THEN NCHAR(0x03b2)
WHEN @C = '6c' THEN NCHAR(0x03b3)
WHEN @C = '6d' THEN NCHAR(0x03b4)
WHEN @C = '6e' THEN NCHAR(0x03b5)
WHEN @C = '6f' THEN NCHAR(0x03b6)
WHEN @C = '70' THEN NCHAR(0x03b7)
WHEN @C = '71' THEN NCHAR(0x03b8)
WHEN @C = '72' THEN NCHAR(0x0eb9)
WHEN @C = '73' THEN NCHAR(0x03ba)
WHEN @C = '74' THEN NCHAR(0x03bb)
WHEN @C = '75' THEN NCHAR(0x03bc)
WHEN @C = '76' THEN NCHAR(0x03be)
WHEN @C = '77' THEN NCHAR(0x03c0)
WHEN @C = '78' THEN NCHAR(0x03c1)
WHEN @C = '79' THEN NCHAR(0x0ec3)
WHEN @C = '7a' THEN NCHAR(0x0ec4)
WHEN @C = '7b' THEN NCHAR(0x03c6)
WHEN @C = '7c' THEN NCHAR(0x03c8)
WHEN @C = '7d' THEN NCHAR(0x03c9)
WHEN @C = '7e' THEN NCHAR(0x0104)
WHEN @C = '7f' THEN NCHAR(0x0105)
WHEN @C = '80' THEN NCHAR(0x0111)
WHEN @C = '81' THEN NCHAR(0x0118)
WHEN @C = '82' THEN NCHAR(0x0119)
WHEN @C = '83' THEN NCHAR(0x011f)
WHEN @C = '84' THEN NCHAR(0x0127)
WHEN @C = '85' THEN NCHAR(0x0132)
WHEN @C = '86' THEN NCHAR(0x0133)
WHEN @C = '87' THEN NCHAR(0x0141)
WHEN @C = '88' THEN NCHAR(0x0142)
WHEN @C = '89' THEN NCHAR(0x014b)
WHEN @C = '8a' THEN NCHAR(0x0150)
WHEN @C = '8b' THEN NCHAR(0x0151)
WHEN @C = '8c' THEN NCHAR(0x0152)
WHEN @C = '8d' THEN NCHAR(0x0153)
WHEN @C = '8e' THEN NCHAR(0x0160)
WHEN @C = '8f' THEN NCHAR(0x0161)
WHEN @C = '90' THEN NCHAR(0x0170)
WHEN @C = '91' THEN NCHAR(0x0171)
WHEN @C = '92' THEN NCHAR(0x0402)
WHEN @C = '93' THEN NCHAR(0x0411)
WHEN @C = '94' THEN NCHAR(0x0414)
WHEN @C = '95' THEN NCHAR(0x0416)
WHEN @C = '96' THEN NCHAR(0x0417)
WHEN @C = '97' THEN NCHAR(0x0418)
WHEN @C = '98' THEN NCHAR(0x0419)
WHEN @C = '99' THEN NCHAR(0x041a)
WHEN @C = '9a' THEN NCHAR(0x041b)
WHEN @C = '9b' THEN NCHAR(0x0423)
WHEN @C = '9c' THEN NCHAR(0x0426)
WHEN @C = '9d' THEN NCHAR(0x0427)
WHEN @C = '9e' THEN NCHAR(0x0428)
WHEN @C = '9f' THEN NCHAR(0x0429)
WHEN @C = 'a0' THEN NCHAR(0x042a)
WHEN @C = 'a1' THEN NCHAR(0x042b)
WHEN @C = 'a2' THEN NCHAR(0x042d)
WHEN @C = 'a3' THEN NCHAR(0x042e)
WHEN @C = 'a4' THEN NCHAR(0x042f)
WHEN @C = 'a5' THEN NCHAR(0x0431)
WHEN @C = 'a6' THEN NCHAR(0x0433)
WHEN @C = 'a7' THEN NCHAR(0x0434)
WHEN @C = 'a8' THEN NCHAR(0x0436)
WHEN @C = 'a9' THEN NCHAR(0x0437)
WHEN @C = 'aa' THEN NCHAR(0x0438)
WHEN @C = 'ab' THEN NCHAR(0x0439)
WHEN @C = 'ac' THEN NCHAR(0x043a)
WHEN @C = 'ad' THEN NCHAR(0x043b)
WHEN @C = 'ae' THEN NCHAR(0x0444)
WHEN @C = 'af' THEN NCHAR(0x0446)
WHEN @C = 'b0' THEN NCHAR(0x0447)
WHEN @C = 'b1' THEN NCHAR(0x0448)
WHEN @C = 'b2' THEN NCHAR(0x0449)
WHEN @C = 'b3' THEN NCHAR(0x044a)
WHEN @C = 'b4' THEN NCHAR(0x044b)
WHEN @C = 'b5' THEN NCHAR(0x044c)
WHEN @C = 'b6' THEN NCHAR(0x044d)
WHEN @C = 'b7' THEN NCHAR(0x044e)
WHEN @C = 'b8' THEN NCHAR(0x044f)
WHEN @C = 'b9' THEN NCHAR(0x0452)
WHEN @C = 'ba' THEN NCHAR(0x0459)
WHEN @C = 'bb' THEN NCHAR(0x04de)
WHEN @C = 'bc' THEN NCHAR(0x04df)
WHEN @C = 'bd' THEN NCHAR(0x04e0)
WHEN @C = 'be' THEN NCHAR(0x04e1)
WHEN @C = 'bf' THEN NCHAR(0x03d1)
WHEN @C = 'c0' THEN NCHAR(0x01c4)
WHEN @C = 'c1' THEN NCHAR(0x01c5)
WHEN @C = 'c2' THEN NCHAR(0x01c6)
WHEN @C = 'c3' THEN NCHAR(0x01c7)
WHEN @C = 'c4' THEN NCHAR(0x01c8)
WHEN @C = 'c5' THEN NCHAR(0x01c9)
WHEN @C = 'c6' THEN NCHAR(0x01ca)
WHEN @C = 'c7' THEN NCHAR(0x01cb)
WHEN @C = 'c8' THEN NCHAR(0x01cc)
WHEN @C = 'c9' THEN NCHAR(0x01ee)
WHEN @C = 'ca' THEN NCHAR(0x01ef)
WHEN @C = 'cb' THEN NCHAR(0x01f1)
WHEN @C = 'cc' THEN NCHAR(0x01f2)
WHEN @C = 'cd' THEN NCHAR(0x01f3)
WHEN @C = 'ce' THEN NCHAR(0x20aa)
WHEN @C = 'cf' THEN NCHAR(0x20ac)
WHEN @C = 'd0' THEN NCHAR(0x0024)
WHEN @C = 'd1' THEN NCHAR(0x00a3)
WHEN @C = 'd2' THEN NCHAR(0x1e00)
WHEN @C = 'd3' THEN NCHAR(0x1e01)
WHEN @C = 'd4' THEN NCHAR(0x1e04)
WHEN @C = 'd5' THEN NCHAR(0x1e05)
WHEN @C = 'd6' THEN NCHAR(0x1e0c)
WHEN @C = 'd7' THEN NCHAR(0x1e0d)
WHEN @C = 'd8' THEN NCHAR(0x1e0e)
WHEN @C = 'd9' THEN NCHAR(0x1e0f)
WHEN @C = 'da' THEN NCHAR(0x1ecc)
WHEN @C = 'db' THEN NCHAR(0x1ecd)
WHEN @C = 'dc' THEN NCHAR(0x1ee4)
WHEN @C = 'dd' THEN NCHAR(0x1ee5)
WHEN @C = 'de' THEN NCHAR(0x1eb8)
WHEN @C = 'df' THEN NCHAR(0x1eb9)
WHEN @C = 'e0' THEN NCHAR(0x1ea0)
WHEN @C = 'e1' THEN NCHAR(0x1ea1)
WHEN @C = 'e2' THEN NCHAR(0x002b)
WHEN @C = 'e3' THEN NCHAR(0x002d)
WHEN @C = 'e4' THEN NCHAR(0x002f)
WHEN @C = 'e5' THEN NCHAR(0x005c)
WHEN @C = 'e6' THEN NCHAR(0x005f)
WHEN @C = 'e7' THEN NCHAR(0x007e)
WHEN @C = 'e8' THEN NCHAR(0x00c0)
WHEN @C = 'e9' THEN NCHAR(0x00c8)
WHEN @C = 'ea' THEN NCHAR(0x00d2)
WHEN @C = 'eb' THEN NCHAR(0x00d9)
WHEN @C = 'ec' THEN NCHAR(0x00e0)
WHEN @C = 'ed' THEN NCHAR(0x00c7)
WHEN @C = 'ee' THEN NCHAR(0x00e8)
WHEN @C = 'ef' THEN NCHAR(0x00ed)
WHEN @C = 'f0' THEN NCHAR(0x00eb)
WHEN @C = 'f1' THEN NCHAR(0x00f2)
WHEN @C = 'f2' THEN NCHAR(0x00f9)
WHEN @C = 'f3' THEN NCHAR(0x00fb)
WHEN @C = 'f4' THEN NCHAR(0x00ff)
WHEN @C = 'f5' THEN NCHAR(0x00c2)
WHEN @C = 'f6' THEN NCHAR(0x00ca)
WHEN @C = 'f7' THEN NCHAR(0x00ce)
WHEN @C = 'f8' THEN NCHAR(0x00db)
WHEN @C = 'f9' THEN NCHAR(0x00e2)
WHEN @C = 'fa' THEN NCHAR(0x00e3)
WHEN @C = 'fb' THEN NCHAR(0x00ea)
WHEN @C = 'fc' THEN NCHAR(0x00ee)
WHEN @C = 'fd' THEN NCHAR(0x00ec)
WHEN @C = 'fe' THEN NCHAR(0x010c)
WHEN @C = 'ff' THEN NCHAR(0x010d)
ELSE NCHAR(0x0023)
END
END
GO
CREATE OR ALTER FUNCTION dbo.MacToLabel (@MAC AS NVARCHAR(max))
RETURNS NVARCHAR(max)
BEGIN
RETURN CONCAT( dbo.CharLookup(SUBSTRING(@MAC, 1, 2))
, dbo.CharLookup(SUBSTRING(@MAC, 4, 2))
, dbo.CharLookup(SUBSTRING(@MAC, 7, 2))
, dbo.CharLookup(SUBSTRING(@MAC, 10, 2))
, dbo.CharLookup(SUBSTRING(@MAC, 13, 2))
, dbo.CharLookup(SUBSTRING(@MAC, 16, 2)))
END
GO
SELECT dbo.MacToLabel('70:D6:68:D9:C6:94')
GO
-- ηḌΨḏNJД
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment