David Burrows (4/29/2014)
Eirikur Eiriksson (4/29/2014)
Probably a string of hex values would be better, at least every character representation is then of the same length.Meh! use octal 😛
What about Base36, almost closes the circle:-D
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(4) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);
;WITH NX(N) AS (SELECT N FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) AS N
FROM NX N1,NX N2
)
SELECT
MYID
,(SELECT
CASE
WHEN ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 36) < 10 THEN CHAR(48 + ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 36))
ELSE CHAR(97 + (((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) / 8) - 10))
END
+ CASE
WHEN ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36) < 10 THEN CHAR(48 + ((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36))
ELSE CHAR(97 + (((UNICODE(SUBSTRING(MS.MYSTRING,NM.N,1))) % 36) - 10))
END
FROM @STRINGS MS
CROSS APPLY NUMBERS NM
WHERE NM.N <= LEN(MS.MYSTRING)
AND MS.MYID = MMS.MYID
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)') AS NUMSTR
FROM @STRINGS MMS
Results
MYID NUMSTR
----------- ---------
1 1c1e3d1v
2 1d1e1z1k
3 1l28341d
4 1j1s3924