Sean Lange (4/29/2014)
What I don't really understand here is what is the point of this? This data is pretty much unusable because you have no way of putting it back together again.
Probably a string of hex values would be better, at least every character representation is then of the same length.
😎
DECLARE @STRINGS TABLE
(
MYID INT IDENTITY(1,1) NOT NULL
,MYSTRING CHAR(5) NOT NULL
)
INSERT INTO @STRINGS (MYSTRING)
SELECT MYSTRING FROM
(VALUES ('02yC'),('12G8'),('9Pp1'),('7@uL')) AS X(MYSTRING);
SELECT
MS.MYID
,MS.MYSTRING
,SUBSTRING(CONVERT(VARCHAR(10),CAST(MS.MYSTRING AS VARBINARY(10)),1),3,10)
FROM @STRINGS MS
Results
MYID MYSTRING HEX_STRING
----------- -------- ----------
1 02yC 30327943
2 12G8 31324738
3 9Pp1 39507031
4 7@uL 3740754C