Converting from byte to char generate minus sign

Database

Converting from byte to char generate minus sign

Hi!

I have a question regarding casting from char to byte using question below.

CREATE VOLATILE TABLE STEP1213_PRICE as (

SELECT 

GenId

,SUBSTR(CAST('00000000000000000000' !! FROM_BYTES(GenId, 'BASE16') AS CHAR(40)), CHARACTER_LENGTH(TRIM(CAST(FROM_BYTES(GenId, 'BASE16' ) AS CHAR(20)))) + 1, 20) AS PartGenCast

from temptable

sample 20

)With Data 

On Commit Preserve Rows ;

sel * from STEP1213_PRICE;

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

Giving result.

 PartGenId                                   PartGenCast

20-00-03-29-00-58-26-01-20-11 20000329005826012011

19-97-06-02-16-38-54-76-28-23 19970602163854762823

07-08-30-00-12-16-00-00-56-94 07083000121600005694

19-98-01-27-14-35-06-57-78-34 19980127143506577834

39-00-00-00-00-00-00-00-00-00 39000000000000000000

19-97-06-02-16-14-47-90-31-33 19970602161447903133

19-98-01-27-14-34-51-28-08-49 19980127143451280849

19-97-06-02-16-43-17-54-82-34 19970602164317548234

20-00-07-18-01-36-31-02-70-66 20000718013631027066

19-99-07-31-00-05-33-02-09-31 19990731000533020931

20-01-04-06-02-08-56-03-39-61 20010406020856033961

19-98-01-27-14-30-46-73-95-87 19980127143046739587

20-00-02-24-11-17-26-79-49-39 20000224111726794939

19-97-06-02-16-40-03-13-04-56 19970602164003130456

93-63-4D-7C-D5-E8-A2-AA-00-00 -6C9CB2832A175D56000

20-00-07-04-08-33-40-00-73-65 20000704083340007365

08-12-16-00-12-06-00-00-20-64 08121600120600002064

19-97-06-02-16-29-39-88-76-00 19970602162939887600

19-97-06-02-16-39-10-76-05-77 19970602163910760577

16-02-22-13-12-28-00-00-08-15 16022213122800000815

I have learned from previous topics that when byte value higher than >= '80' it needs special handling. 

My question is how to apply this on my question above. 

Thanks in advance

//D


Tags (1)
5 REPLIES

Re: Converting from byte to char generate minus sign

And now I see that the complete fault string is changed from it´s original. It´s not just the minus (-) sign that is added

N/A

Re: Converting from byte to char generate minus sign

@denilsson10:

You need to add a leading binary zero to GenId: 

FROM_BYTES('00'xb||GenId,  'BASE16')

And to get a fixed size with leading zeroes you better use LPAD:

LPAD(FROM_BYTES('00'xb||GenId,  'BASE16'), 20)

@rshivha:

Go to the main page of the forum where you want to post and simply click the "CREATE A NEW FORUM TOPIC" button.

WHERE CAST(logondatetime AS DATE)  BETWEEN    '2015-04-20 ' AND  '2016-04-20'

is the same as 

WHERE CAST(logondatetime AS DATE) = DATE '2016-04-20'

-> return the data from this date

WHERE logondatetime > Timestamp '2015-04-20 00:00:00'

Assuming there's no data from tomorrow in DBQL it's the same, too (ok, almost the same, should be >=)

Re: Converting from byte to char generate minus sign

@dnoeth

Thanks, it works just fine!! :-)

Br//David

Re: Converting from byte to char generate minus sign

@dnoeth

It works fine as long as the conversion is not handling values starting with 'FF-FF-........' in that case it looks like the attached file.

FF is the highest value, is it to be treated in a special way?

I dont get it!

Thanks in advance

N/A

Re: Converting from byte to char generate minus sign

SELECT 'FFFF4D7CD5E8A2AA0000'xb AS GenId
,LPAD(FROM_BYTES('00'xb||GenId, 'BASE16'), 20)

returns

FF-FF-4D-7C-D5-E8-A2-AA-00-00 FFFF4D7CD5E8A2AA0000