put ascii value into a variable

Question

Is it Available to put ascii values into variable in sql server 2008 r2? I trying this code:

declare @thechars varchar(15)
declare @theascii varchar(50)
declare @position int

set @thechars = 'somechar'
set @theascii = ''
set @position = 1

while(@position<16)
begin
   set @theascii = @theascii+convert(varchar,ascii(substring(@thechars ,@position,1)))
   set @position = @position + 1
end

print '-------------------------'
print 'the ascii is ' + @theascii 
print '-------------------------'

but the result just like this:

-------------------------   

-------------------------

is there any way to solve my problem?

To troublehoot, output some values inside your loop so you can see what is going on. — Dan Bracuk, Jul 05 '14 at 11:39

score 2 · Answer 1 · answered Jul 05 '14 at 10:21

2

Use

while(@position <= LEN(@thechars))

I have no idea why you hard code 16 in your version.

Trying to extract a SUBSTRING after the length of the string returns NULL which then means the whole concatenated string is NULL.

If you must do that then an alternative is to replace NULL with empty string prior to concatenation.

SET @theascii += ISNULL(CONVERT(VARCHAR(3),ASCII(SUBSTRING(@thechars ,@position,1))),'')

answered Jul 05 '14 at 10:21

Martin Smith

438,706
87
741
845

Thanks. It works. I think I should try to not hard code my program from now. – user2265229 Jul 06 '14 at 05:17

score 0 · Answer 2 · answered Jul 05 '14 at 19:32

0

It seems that you are trying to do something like this following.

NB, I've included the idea of a Tally\Numbers table as opposed to using a loop. Tally tables are typically regarded as being more performant. Please read up about them.

DECLARE  @InputText     VARCHAR(50)     = 'Hello, world!'
        ,@ASCIIString   VARCHAR(200)    = ''


;WITH TallyCTE (n) AS
--  You may want to create a physical Tally table - Google & read up!
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM syscolumns
)
SELECT @ASCIIString = @ASCIIString + CAST(ASCII(SUBSTRING(@InputText, n, 1)) AS VARCHAR) + ', '
FROM TallyCTE
WHERE n <= LEN(@InputText)


PRINT 'The ASCII lookup values are; ' + LEFT(@ASCIIString, LEN(@ASCIIString)-1)

answered Jul 05 '14 at 19:32

MarkD

5,276
1
14
22

This approach [isn't guaranteed to work](http://stackoverflow.com/a/15163136/73226). You would need another approach e.g. `XML PATH` – Martin Smith Jul 05 '14 at 19:57
True, but the process can be modified to show each ascii lookup code for each character, making ordering unnecessary. – MarkD Jul 06 '14 at 06:30
It isn't guaranteed to work (and sometimes doesn't) even with no order by in sight. – Martin Smith Jul 06 '14 at 09:24

score -1 · Answer 3 · answered Jul 05 '14 at 20:07

-1

Do not hardcode the length in WHILE LOOP. Sometimes the given input length may exceeds your hardcoded value. But still you will get some output. If the length is lesser than the value, you will get the empty value. If you use 'SELECT' statement instead of PRINT you will get NULL as output.

Handle this issue

Replace the hardcoded value with LEN(@thechars)

Or

Handle NULL : ISNULL(CONVERT (VARCHAR,ASCII(SUBSTRING(@thechars ,@position,1))),'')

First one is best

answered Jul 05 '14 at 20:07

Jesuraja

3,774
4
24
48

How is this different from the two points I made 9 hours previously? – Martin Smith Jul 05 '14 at 20:11
Sorry I used mobile device. While scrolling your answer skipped. Just only saw your answer. Your is good explanation. – Jesuraja Jul 05 '14 at 20:41

put ascii value into a variable

3 Answers3