hex conversion

Question

hex conversion

HeftSteady

Old Hand

Points: 343
More actions
November 19, 2022 at 4:14 am

#4115149
I am trying to convert some numbers pulled from a char field.
I would like to pull the 1 and 46 from the 000.001.070.000.0 to return as 1.46 .
I tried a couple of things but not quite hitting the mark.
create table Models (
Modelid integer,
Modelnameid char(17)
);
insert into Models values ( 1 , '000.001.070.000.0' );
select format(cast(SUBSTRING(Modelnameid , 10, 2) as numeric), 'x')
,cast(SUBSTRING(Modelnameid , 10, 2) as numeric)
,convert(varbinary(12),(cast(SUBSTRING(Modelnameid , 10, 2) as numeric)))
,Modelnameid
from Models;
- This topic was modified 2 years ago by HeftSteady.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Why does this make sense? CONVERT using style 2 seems to convert to text and removes the leading '0x'

select Modelnameid origignal
      ,cast(SUBSTRING(Modelnameid , 10, 2) as numeric) substr_num
      ,convert(varbinary(12),(cast(SUBSTRING(Modelnameid , 10, 2) as numeric))) conv_vb
      ,cast(v.conv_to_vb as char(16)) cast_as_char
      ,v.conv_to_vb
      ,convert(char(16), v.conv_to_vb, 2) conv_vb_to_string
      ,stuff(substring(convert(char(16), v.conv_to_vb, 2), 8, 3), 2, 0, '.') stuff_conv_vb_to_string
from #Models
     cross apply (values (convert(varbinary(12),(cast(SUBSTRING(Modelnameid , 10, 2) as numeric))))) v(conv_to_vb);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

HeftSteady wrote:

I would like to pull the 1 and 46 from the 000.001.070.000.0 to return as 1.46 .

~~I'm totally lost here... where do you see a "46" in the "000.001.070.000.0" string? Are you saying that you want to convert the 070 to a hex 46?~~

Never mind... I figured it out...

This reply was modified 2 years ago by Jeff Moden. Reason: NM. Figured it out

--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: 1004339 More actions · Answer 3

Ok... when you have a ModelNameID like this...

000.001.002.000.0

... or like this...

000.160.170.000.0

... what do you want to return?

My thought is that it's a form of future computational suicide to drop the leading zeros on any hex conversion.

--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)