February 9, 2009 at 10:44 am
I am having trouble updating a column due to the fact it is a bit field with an image data type. Users have the following data in the gui "Quality REV_A 1,3,8,10". I want to replace "REV_A" with "REV_B".
I tried the following with no luck.
UPDATE PART_PO_BINARY SET BITS = '%REV_B%' WHERE BITS LIKE '%REV_A%'
However, I realize that I somehow need to convert the data to an image but I'm not sure how to get there.
Any help would be greatly appreciated.
February 9, 2009 at 11:19 am
I may be missing something. First you say it's a bit field, then you say it's a binary (image) field, then you're using string functions on it. Also, the content doesn't look like binary data, from your example.
If it's a string, you can use Replace to accomplish what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 12:11 pm
The BITS column is an image data type. When queried it returns "0x5175616C697479205245565F4120312C332C352C37". However, when you view it through the gui it shows "Quality REV_A 1,2,3,8". Their asking me to go in and update the REV_A to REV_B, but I have no clue how to accomplish this.
Another question: If I create a view and convert the image to a varchar, can I update the view to reflect changes in the main table? Just a thought. I'm reaching!
Here is the output from the PART_PO_BINARY table:
ROWIDPART_ID TYPEBITS BITS_LENGTH
41133500-1D0x5175616C697479205245565F4120312C332C352C3722
Here is the out put from my view if I convert the data type of the BITS field to varchar:
PART_ID BITS
1133500-1Quality REV_A 1,3,5,7
February 9, 2009 at 12:32 pm
Here is the output from the PART_PO_BINARY table:
ROWIDPART_ID TYPEBITS BITS_LENGTH
41133500-1D0x5175616C697479205245565F4120312C332C352C3722
Here is the out put from my view if I convert the data type of the BITS field to varchar:
PART_ID BITS
1133500-1Quality REV_A 1,3,5,7
The column data type is actually binary so to update how it appears as a VARCHAR(50), you need to do two conversions. Converting your original column to VARCHAR, do the string replace, and then converting back to binary. Your update needs to look something like this
UPDATE PART_PO_BINARY SET BITS = CONVERT(BINARY, REPLACE(CONVERT(VARCHAR(50), BITS),'REV_A','REV_B'))
February 9, 2009 at 12:38 pm
I said varchar but I actually used HexToChar to convert the image.
select PART_ID, dbo.HexToChar(BITS) as BITS
from PART_PO_BINARY
How should my update statement look using HexToChar?
February 9, 2009 at 12:47 pm
bpowers (2/9/2009)
I said varchar but I actually used HexToChar to convert the image.select PART_ID, dbo.HexToChar(BITS) as BITS
from PART_PO_BINARY
How should my update statement look using HexToChar?
I'm not exactly sure what dbo.HexToChar does in your database but assuming it returns a string you could switch out the conversion to VARCHAR with that so it would be:
UPDATE PART_PO_BINARY SET BITS = CONVERT(BINARY, REPLACE(dbo.HexToChar(BITS),'REV_A','REV_B'))
February 9, 2009 at 12:58 pm
Worked like a charm. I appreciate it very much!
February 9, 2009 at 12:59 pm
Glad to hear it. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply