Convert Varchar to Ascii

Question

I'm trying to convert the contents of a VARCHAR field to be unique number that can be easily referenced by a 3rd party.

How can I convert a varchar to the ascii string equivalent? In TSQL? The ASCII() function converts a single character but what can I do to convert an entire string?

I've tried using

CAST(ISNULL(ASCII(Substring(RTRIM(LTRIM(PrimaryContactRegion)),1,1)),'')AS VARCHAR(3))
+ CAST(ISNULL(ASCII(Substring(RTRIM(LTRIM(PrimaryContactRegion)),2,1)),'')AS VARCHAR(3))

....but this is tedious, stupid looking, and just doesn't really work if I had long strings. Or if it is better how would I do the same thing in SSRS?

How long might the string to be converted be? Anything past 3 characters gets real ugly real fast. — Philip Kelley, Dec 20 '10 at 17:41
Certainly long enough that I will need to seek out something different. I have a couple 30 character strings. — Bolt_Head, Dec 20 '10 at 17:48
This works only up to 100 as the recursion limit. If you have a string longer than 100, it will stop execution after hitting the limit — Yeh Shenhua, Mar 10 '15 at 21:43

KM. · Accepted Answer · 2010-12-20T20:24:41.680

try something like this:

DECLARE @YourString   varchar(500)

SELECT @YourString='Hello World!'

;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<LEN(@YourString)
)
SELECT
       (SELECT
            ASCII(SUBSTRING(@YourString,Number,1))
            FROM AllNumbers
            ORDER BY Number
            FOR XML PATH(''), TYPE
       ).value('.','varchar(max)') AS NewValue
       --OPTION (MAXRECURSION 500) --<<needed if you have a string longer than 100

OUTPUT:

NewValue
---------------------------------------
72101108108111328711111410810033

(1 row(s) affected)

just to test it out:

;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<LEN(@YourString)
)
SELECT SUBSTRING(@YourString,Number,1),ASCII(SUBSTRING(@YourString,Number,1)),* FROM AllNumbers

OUTPUT:

                 Number
---- ----------- -----------
H    72          1
e    101         2
l    108         3
l    108         4
o    111         5
     32          6
W    87          7
o    111         8
r    114         9
l    108         10
d    100         11
!    33          12

(12 row(s) affected)

Also, you might want to use this:

RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)

to force all ASCII values into 3 digits, I'm not sure if this is necessary based on your usage or not.

Output using 3 digits per character:

NewValue
-------------------------------------
072101108108111032087111114108100033

(1 row(s) affected)

If anyone is using this code with a string that might contain trailing spaces, I'd recommend replacing all uses of the `LEN()` function with `DATALENGTH()`. — AHiggins, Feb 16 '15 at 14:24
This is excellent. I made a small change, to make it a little easier to read, should that be necessary. `RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3) + ', '` — RPh_Coder, Feb 25 '19 at 22:49

score 1 · Answer 2 · answered Dec 20 '10 at 18:19

Well, I think that a solution to this will be very slow, but i guess that you could do something like this:

DECLARE @count INT, @string VARCHAR(100), @ascii VARCHAR(MAX)

SET @count = 1
SET @string = 'put your string here'
SET @ascii = ''

WHILE @count <= DATALENGTH(@string)
BEGIN
    SELECT @ascii = @ascii + '&#' + ASCII(SUBSTRING(@string, @count, 1)) + ';'
    SET @count = @count + 1
END

SET @ascii = LEFT(@ascii,LEN(@ascii)-1)
SELECT @ascii

I'm not in a pc with a database engine, so i can't really test this code. If it works, then you can create a UDF based on this.

You need a CAST or CONVERT statement around the ASCII part of the main SELECT statement for this solution to work e.g. `CAST(ASCII(SUBSTRING(@string, @count, 1)) AS VARCHAR)` — StuKay, Aug 27 '18 at 11:57

Convert Varchar to Ascii

2 Answers2

Linked