How to select the ascii from a column

Question

Post reply

How to select the ascii from a column

StuDev

Old Hand

Points: 340
More actions
February 19, 2010 at 3:58 am

#386346

Dear All,
I have a table with two columns:
name and number...the type of both columns is varchar.
now how can ik select the both columns buth the value of column number most be returned in ascii.
Please some help.
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

Post the table schema,

Are you sure, its shows the ASCII values for the both the columns?

StuDev Old Hand Points: 340 More actions · Answer 2

Hi,

here is it:

CREATE TABLE [dbo].[customer](

[name] [varchar](50) NULL,

[number] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

the columns are varchar. i want to select both columns buth from the number column the ascii code.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

So if number = "1234" you want "49,50,51,52" ???

Please give a decent example.

Clear Sky SQL
My Blog[/url]

StuDev Old Hand Points: 340 More actions · Answer 4

StuDev

Old Hand

Points: 340

February 19, 2010 at 5:07 am

#1121726

Yes, this is exatly what i want 🙂

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Can you show us what you have tried so for ?

What is your stumbling block in your own experiments ?

Clear Sky SQL
My Blog[/url]

StuDev Old Hand Points: 340 More actions · Answer 6

i tied:

SELECT ASCII('number') from customer...bud this returns only the ascii of the first character

select cast(number as varbinary(40)) from table ...bud this returns hex

StuDev Old Hand Points: 340 More actions · Answer 7

i tried:

SELECT ASCII('number') from customer...bud this returns only the ascii of the first character

select cast(number as varbinary(40)) from table ...bud this returns hex

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

as you have discovered , Ascii will return the Ascii value of a single character.

So first of you need to cut the string into its single characters.

A good example of how to do that is given here[/url].

Once you have done that you need to rejoin them values back into on string

Again a good reference is here[/url]

Let us know how you get on

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 9

This might give you some ideas too:

-- Test table

CREATE TABLE #Test (row_id INTEGER PRIMARY KEY, data VARCHAR(50) COLLATE LATIN1_GENERAL_BIN NOT NULL);

-- Sample data

INSERT #Test (row_id, data) VALUES (1, '1234567890');

INSERT #Test (row_id, data) VALUES (2, '0987654321');

INSERT #Test (row_id, data) VALUES (3, '8176');

-- Magic

WITH

-- Number generator

L0 AS (SELECT 1 AS C UNION ALL SELECT 1),

L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS (SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),

Numbers

AS

(

SELECT TOP (SELECT MAX(DATALENGTH(data)) FROM #Test)

ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N

FROM L4

ORDER BY N

)

SELECT sequence = NUM.N,

character = SUBSTRING(T.data, N, 1),

character_code = ASCII(SUBSTRING(T.data, N, 1))

FROM #Test T

JOIN Numbers NUM

ON NUM.N BETWEEN 1 AND DATALENGTH(T.data)

ORDER BY

T.row_id,

NUM.N;

GO

-- Tidy up

DROP TABLE #Test;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi