I think the easiest way is going to be by casting it to VARBINARYA(MAX) and then manually going through.
So something like this:
SELECT CAST('HELLO' AS VARBINARY(MAX))
Which gives you:
0X is telling me that it is in HEX, so it can be discarded. 48 is H, 45 is E, 4C is L and 4F is O. Every 2 characters in the VARBINARY is 1 letter in the VARCHAR. Now, if your character string is NVARCHAR, then you end up with something more like this:
Here, every 4 characters is 1 character, so 4800 is H, 4500 is E and so on.
BUT once you have it in VARBINARY, it is just a matter of narrowing things down to determine which CHAR value to use. For example, CHAR(48) will give you an H and so on so the following:
SELECT CHAR(0x48) + CHAR(0x45) + CHAR(0x4C) + CHAR(0x4C) + CHAR(0x4F)
For NCHAR you need to swap the bits a bit. So:
SELECT NCHAR(0x0048) + NCHAR(0x0045) + NCHAR(0x004C) + NCHAR(0x004C) + NCHAR(0x004F)
ALTERNATELY, if you don't like working with HEX in CHAR and NCHAR, you can convert those to DECIMAL. So 48 becomes 72, 45 becomes 69, 4C becomes 76, and 4F becomes 79 so:
SELECT CHAR(72) + CHAR(69) + CHAR(76) + CHAR(76) + CHAR(79)