Convert hex to text SQL 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert hex to text SQL 2008

Author

Topic

fdr76
Starting Member

5 Posts

Posted - 2010-11-12 : 07:57:28

Hello,

With the command: SELECT CONVERT(VARCHAR(8000),0x43) I get the response C

now I want to show the text in place of the hex-value which are stored in column OMS in a SQL 2008 Database. I thought that the follow query is the correct one but I get as responce 0x43 instead of C.

select convert(VARCHAR(8000), OMS)from INFO

Can someone tell me what I do wrong?

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 08:07:01

Can you post some data which is present in OMS?

PBUH

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-12 : 08:11:27

What is datatype of OMS columns.
I feel it may be varchar.
In case it is varchar then Varchar when converted to varchar returns same value.

You need to use some function to convert the hexadecimal value stored as varchar.

If the datatype is something different then please let us know.

Also have a look at:
http://support.microsoft.com/kb/104829

fdr76
Starting Member

5 Posts

Posted - 2010-11-12 : 08:14:00

quote:
Originally posted by Sachin.Nand

Can you post some data which is present in OMS?

Table INFO
ID OMS
1 0x74657374
2 0x43

with a query I wan't to see the text value. With the query
SELECT CONVERT(VARCHAR(8000),0x74657374)I get test.

Now I wan't a query to see the text value of the OMS data in the table INFO.
I thouht that i can do that with te query select id, convert(VARCHAR(8000), OMS)from INFO

the collomn OMS is varchar(8000)

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 08:19:37

[code]
declare @t table( ID int,OMS VARCHAR(40))
insert @t
select 1 ,0x74657374 union
select 2 ,0x43

select id,OMS from @t
[/code]

PBUH

fdr76
Starting Member

5 Posts

Posted - 2010-11-12 : 08:26:49

quote:

PBUH

Thank you for the fast response. The biggist problem is that I have 30.000 values in the collumn OMS. Is there a way to automatically show the good values

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 08:28:18

But what is your definition of good values?

PBUH

fdr76
Starting Member

5 Posts

Posted - 2010-11-12 : 08:29:33

quote:
Originally posted by Sachin.Nand

But what is your definition of good values?

the text value and not the hex one.

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 08:31:51

quote:
Originally posted by fdr76

quote:
Originally posted by Sachin.Nand

But what is your definition of good values?

the text value and not the hex one.

Show some example the way I did in my previous post.

PBUH

fdr76
Starting Member

5 Posts

Posted - 2010-11-12 : 08:39:44

Show some example the way I did in my previous post.

PBUH

ID OMS
1 text
2 C

but in the query you gave the hex vaulues are hard typed. I wan't the fill these with a query.

Part of my INFO table (total 30.000 items):
ID OMS
3 0x62696b65
4 0x636172
5 0x686f757365

I have changed the query to:
declare @t table( ID int,OMS VARCHAR(8000))
insert @t
select id, OMS from INFO
select id,OMS from @t

and i get:
id OMS
3 0x62696b65
4 0x636172
5 0x686f757365

when I use
declare @t table( ID int,OMS VARCHAR(40))
insert @t
select 1 ,0x62696b65 union
select 2 ,0x636172
select id,OMS from @t

I get:
ID OMS
1, bike
2, car

the problem is that with the select query the values not converted and when I type them one for one it works. My quastion why?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources