Covert all characters in field into their ASCII code

Question

Post reply

Covert all characters in field into their ASCII code

Viewing 11 posts - 16 through 25 (of 25 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

ScottPletcher (4/29/2014)
Simple problems sometimes call for simple solutions. Why over-engineer everything?

I do agree but sometimes I get carried away on the cloud of perfection and everlasting solutions:-P

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

ScottPletcher (4/29/2014)
Using XML on five bytes sure seems like folderol to me.

You say "folderol" I say "flexible", let's call the whole thing off. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

ScottPletcher (4/29/2014)
For only 5 chars, I wouldn't bother will all the CTEs and related folderol.
Why not just:
SELECT
ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 2, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 3, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 4, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 5, 1)) AS varchar(3)), '')
FROM (
SELECT '02yC' AS data UNION ALL
SELECT '12G8' UNION ALL
SELECT '9Pp1' UNION ALL
SELECT '7@uL' UNION ALL
SELECT '' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b2' UNION ALL
SELECT 'c34' UNION ALL
SELECT 'd456' UNION ALL
SELECT 'e5678'
) AS test_data

]

That will likely be faster but run the following and see why I wouldn't allow such a thing.

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

ISNULL(CAST(ASCII(SUBSTRING(data, 2, 1)) AS varchar(3)), '') +

ISNULL(CAST(ASCII(SUBSTRING(data, 3, 1)) AS varchar(3)), '') +

ISNULL(CAST(ASCII(SUBSTRING(data, 4, 1)) AS varchar(3)), '') +

ISNULL(CAST(ASCII(SUBSTRING(data, 5, 1)) AS varchar(3)), '')

FROM (

SELECT '02yC' AS data UNION ALL

SELECT '12G8' UNION ALL

SELECT '9Pp1' UNION ALL

SELECT '7@uL' UNION ALL

SELECT '' UNION ALL

SELECT 'a' UNION ALL

SELECT 'b2' UNION ALL

SELECT 'c34' UNION ALL

SELECT 'd456' UNION ALL

SELECT 'e5678' UNION ALL

SELECT 'f90123'

) AS test_data

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

Jeff Moden (4/29/2014)
ScottPletcher (4/29/2014)
For only 5 chars, I wouldn't bother will all the CTEs and related folderol.
Why not just:
SELECT
ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 2, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 3, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 4, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 5, 1)) AS varchar(3)), '')
FROM (
SELECT '02yC' AS data UNION ALL
SELECT '12G8' UNION ALL
SELECT '9Pp1' UNION ALL
SELECT '7@uL' UNION ALL
SELECT '' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b2' UNION ALL
SELECT 'c34' UNION ALL
SELECT 'd456' UNION ALL
SELECT 'e5678'
) AS test_data
]
That will likely be faster but run the following and see why I wouldn't allow such a thing.
SELECT
ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 2, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 3, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 4, 1)) AS varchar(3)), '') +
ISNULL(CAST(ASCII(SUBSTRING(data, 5, 1)) AS varchar(3)), '')
FROM (
SELECT '02yC' AS data UNION ALL
SELECT '12G8' UNION ALL
SELECT '9Pp1' UNION ALL
SELECT '7@uL' UNION ALL
SELECT '' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b2' UNION ALL
SELECT 'c34' UNION ALL
SELECT 'd456' UNION ALL
SELECT 'e5678' UNION ALL
SELECT 'f90123'
) AS test_data

I did run it. We all realize the values can't be "reversed" into the original string, but that may not matter here. Perhaps OP just wants some type of hash value or something.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Reversal isn't the point I was trying to make. Your code did silent truncation on that last value (has 6 characters) which represents what can happen over time with any column supposedly "guaranteed" to be a certain width.

Don't get me wrong... your non-over-engineered code will likely run faster than any Tally Table method, in this case, but I'd at least put a check in to check for max length.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

LoosinMaMind (4/29/2014)
How can I convert each character into its ASCII equivalent?
Expected Results
485012167
49507156
578011249
556411776

Does the result need to be reversable and/or totally unique if totally unique orginal values are provided?

For example, 1212121212, how many values can you come up with that will have that as the ASCII "equivalent"? There's certainly more than 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 7

Hi,

Sorry for the delay in responding (meetings Urgghh!!! )

i have yet to try the suggested solutions. I will let you know how i get on.

One of you asked as to why i would be doing this and the reason is as follows..

I am transfering a set of data from one system to another and appending the orig data to an existing table where i can. The orig data in the table is a varchar (10) and i have to populate a field that is numeric (18,0).

My thinking was that by converting all acharacters to their ASCII value, I would then have a number to populate the existing field.

If anyone knows of a better solution please advise.

Regards

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Do you have the option of adding a table and use the numeric column to link the two? ASCII code in base 10 is not going to be too useful unless you limit the characters to < 100.

😎

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 9

Never more than 5 characters as per 1st post.

so the ASCII string will be at most 15 charcters long i guess thus not causinga problem for the numeric (18)

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

LoosinMaMind (4/30/2014)
Hi,
Sorry for the delay in responding (meetings Urgghh!!! )
i have yet to try the suggested solutions. I will let you know how i get on.
One of you asked as to why i would be doing this and the reason is as follows..
I am transfering a set of data from one system to another and appending the orig data to an existing table where i can. The orig data in the table is a varchar (10) and i have to populate a field that is numeric (18,0).
My thinking was that by converting all acharacters to their ASCII value, I would then have a number to populate the existing field.
If anyone knows of a better solution please advise.
Regards

But what is the desired result for this? The data is unusable because you can't reverse it back to the original value. This is really no different than just shoving in some random numbers. You will have useless data in the target so why bother with all this folderol as Scott stated earlier? 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

LoosinMaMind (4/30/2014)
Never more than 5 characters as per 1st post.
so the ASCII string will be at most 15 charcters long i guess thus not causinga problem for the numeric (18)
Thanks

To be honest, it sounds like an unnecessary transformation. I'd continue to use just the original 5 charcters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)