February 15, 2018 at 10:47 am
Hello
I have a scenario where in I am using a Hex value, to get a meaningful value I need to append 00 to the original hex value and then reverse it and convert to integer
Can some one suggest me a better way to do this, I am using the following Query but am unable to get any results.
select reverse(Data1+data2) -- where data1 is the original hex value and data2 is 00 Thanks in advance.
IQ
February 16, 2018 at 2:19 am
IQ1 - Thursday, February 15, 2018 10:47 AMHello
I have a scenario where in I am using a Hex value, to get a meaningful value I need to append 00 to the original hex value and then reverse it and convert to integer
Can some one suggest me a better way to do this, I am using the following Query but am unable to get any results.select reverse(Data1+data2) -- where data1 is the original hex value and data2 is 00 Thanks in advance.
IQ
Why not convert straight to numerical data type?
😎
DECLARE @MHEX VARBINARY(2) = 0x80;
SELECT
CONVERT(INT,@MHEX,0) AS INT_VAL;
Output
INT_VAL
128
February 16, 2018 at 4:39 am
IQ1 - Thursday, February 15, 2018 10:47 AMHello
I have a scenario where in I am using a Hex value, to get a meaningful value I need to append 00 to the original hex value and then reverse it and convert to integer
Can some one suggest me a better way to do this, I am using the following Query but am unable to get any results.select reverse(Data1+data2) -- where data1 is the original hex value and data2 is 00 Thanks in advance.
IQ
It would be helpful if you posted the original Hex value and the expected integer result for people to be able to hit the mark you're expecting.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2018 at 5:13 am
Jeff Moden - Friday, February 16, 2018 4:39 AMIQ1 - Thursday, February 15, 2018 10:47 AMHello
I have a scenario where in I am using a Hex value, to get a meaningful value I need to append 00 to the original hex value and then reverse it and convert to integer
Can some one suggest me a better way to do this, I am using the following Query but am unable to get any results.select reverse(Data1+data2) -- where data1 is the original hex value and data2 is 00 Thanks in advance.
IQIt would be helpful if you posted the original Hex value and the expected integer result for people to be able to hit the mark you're expecting.
I'm curious about why the reverse!
It's a bit obtuse for endian :ermm:
Far away is close at hand in the images of elsewhere.
Anon.
February 16, 2018 at 8:01 am
Thanks for your suggestions, hope this table helps in explaining what I am looking for.
| Initial Value | Appending 00 to the value
| Reversed Reding( Final Reading required)
|
| 0xE3C8F405
| 0x05F4C8E3
| 0x05F4C8E3
|
| 0x6F4001
| 0x6F400100
| 0x0001406F
|
| 0x10FB
| 0x10FB0000
| 0x0000FB10
|
| 0xE5
| 0xE5000000
| 0x000000E5
|
February 16, 2018 at 8:14 am
So it looks like it's a formatting thing, not any sort of manipulation of the data itself?
You're trying to get the existing hex data to be 8 characters (plus the 0x?)
If that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.
February 16, 2018 at 8:20 am
I know SQL is not intended to do this but for sure there may be a way to do in SQL also.
February 16, 2018 at 8:23 am
jasona.work - Friday, February 16, 2018 8:14 AMSo it looks like it's a formatting thing, not any sort of manipulation of the data itself?
You're trying to get the existing hex data to be 8 characters (plus the 0x?)If that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.
+1
However, a possibility
SELECT [value],
CONVERT(varbinary(8),'0x'+
REPLACE(SUBSTRING(CONVERT(char(8), [value], 2),7,2),' ','00')+
REPLACE(SUBSTRING(CONVERT(char(8), [value], 2),5,2),' ','00')+
REPLACE(SUBSTRING(CONVERT(char(8), [value], 2),3,2),' ','00')+
REPLACE(SUBSTRING(CONVERT(char(8), [value], 2),1,2),' ','00')
,1)
FROM (VALUES
(CAST(0xE3C8F405 as varbinary)),
(CAST(0x6F4001 as varbinary)),
(CAST(0x10FB as varbinary)),
(CAST(0xE5 as varbinary))
) a ([value]);
Far away is close at hand in the images of elsewhere.
Anon.
February 16, 2018 at 8:27 am
jasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.
I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2018 at 8:45 am
Jeff Moden - Friday, February 16, 2018 8:27 AMjasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
Unless its done on a large table, in a loop, row-by-row, in the OLTP, during the heaviest business hours, making the poor production server scream for help.
February 16, 2018 at 8:45 am
Another possibility...
SELECT Original = hv.HexValue
,Final = CONVERT(BINARY(4),REVERSE(CONVERT(BINARY(4),(hv.HexValue))))
FROM (--==== This is just test data
VALUES
(0xE3C8F405)
,(0x6F4001)
,(0x10FB)
,(0xE5)
) hv (HexValue)
;
Results:
Original Final
---------- ----------
0xE3C8F405 0x05F4C8E3
0x6F4001 0x0001406F
0x10FB 0x0000FB10
0xE5 0x000000E5
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2018 at 8:46 am
Jeff Moden - Friday, February 16, 2018 8:27 AMjasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
I don't know that I'd agree that adding leading zeroes to a hex value falls under the intent of DML...
I'm not saying it's not possible (after all, there's already at least one possible solution posted here, but to my mind, DML would be converting those hex values to integers, or character strings, not padding them with leading zeroes...
February 16, 2018 at 8:49 am
ManicStar - Friday, February 16, 2018 8:45 AMJeff Moden - Friday, February 16, 2018 8:27 AMjasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
Unless its done on a large table, in a loop, row-by-row, in the OLTP, during the heaviest business hours, making the poor production server scream for help.
Heh... Ok... I'll disagree with that. 😉 Such simple manipulations are a whole lot less expensive than having an outside source have to read from the server and then put it back not to mention the cost of developing external code, testing it, and maintaining it. There are also a whole lot of times when such things don't have a GUI readily available like when converting ETL imports, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2018 at 8:56 am
jasona.work - Friday, February 16, 2018 8:46 AMJeff Moden - Friday, February 16, 2018 8:27 AMjasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
I don't know that I'd agree that adding leading zeroes to a hex value falls under the intent of DML...
I'm not saying it's not possible (after all, there's already at least one possible solution posted here, but to my mind, DML would be converting those hex values to integers, or character strings, not padding them with leading zeroes...
It's a simple conversion to normalize some data that's no different than casting GETDATE() to a DATE datatype to strip off the time element. You wouldn't send that little bit of computational heaven to the GUI, would you?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2018 at 9:05 am
Jeff Moden - Friday, February 16, 2018 8:56 AMjasona.work - Friday, February 16, 2018 8:46 AMJeff Moden - Friday, February 16, 2018 8:27 AMjasona.work - Friday, February 16, 2018 8:14 AMIf that's the case, it might be better to do that in the front-end (application) code, it's not really something SQL is intended to do.I'll never understand why people say that such data manipulation is "not really something SQL is intended to do". The acronym of "DML" stands for "Data Manipulation Language". 😉
I don't know that I'd agree that adding leading zeroes to a hex value falls under the intent of DML...
I'm not saying it's not possible (after all, there's already at least one possible solution posted here, but to my mind, DML would be converting those hex values to integers, or character strings, not padding them with leading zeroes...
It's a simple conversion to normalize some data that's no different than casing GETDATE() to a DATE datatype to strip off the time element. You wouldn't send that little bit of computational heaven to the GUI, would you?
OK, chalk up my response to not being familiar with how hex is handled in SQL (I'm a production DBA, not a Dev DBA... 😉 )
Seeing as SQL sees, and "stores" 0xE5 and 0x000000E5 the same, obviously I was heading down the wrong track.
Learn something new every day!
Oh, and no, I wouldn't expect someone to have to use the front end to strip the time from a datetime field (or displaying just the date from a getdate())
😀:hehe:
But, just to annoy you, Jeff, if they did it by converting the getdate() to a string, reversing it, then using a cursor to loop through until they reached the end of the time and collected the remaining characters before reversing it again and then converted it to a date, I'd be fine with that...
:hehe::hehe:
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply