How to do this ?
-
Hi, SQL gurus,
I have a problem when try to update a table.
We have an [ITEM] table with structure :
CREATE TABLE [ITEM] (
[ITEMNO] [char] (20) NOT NULL ,
[ITEMINFO] [char] (60) NOT NULL
....
)
The [ITEMINFO] contains some important info about an item, one of which is the "Whole Sale Price". The application we will use treats it as "secret" and embeds it in the [ITEMINFO]. When I use
Select ITEMINFO, ITEMNO from ITEM Where ITEMNO = '3000'
I get :
ITEMINFO ITEMNO
------------------------------------------------------------ ------
FL 01 0 Ý 00000000 00V 3000
Note that there are some special characters inside the ITEMINFO string, this is the "Whole Sale Price".
I know I can use:
Select
CAST(CAST(SUBSTRING(ITEMINFO, 30,4) AS VARBINARY ) AS BIGINT) from ITEM Where ITEMNO = '3000'
to get the result 1245 ( whole sale price = $12.45 ). But I would like to update the value to another one ( say 1875, i.e. $18.75). How can I do it and still keep the price as "secret" in the string at the same positions?
I have been trying to use:
Update ITEM
Set ITEMINFO = Substring(ITEMINFO, 1, 29) +
Char(18) + Char(75) +
Substring(ITEMINFO, 34, 26) where ITEMNO = '3000'
but the result is not right ( by using the select query above ).
Please Help !
Thank you in advance.
Jim
-
What if the wholesale price goes above $255 and you can't Char() it ?
I'm sure I won't be the 1st to ask "Why?" nor will I be the first to point out how this breaks normalization rules and is a very flawed design.
-
This is the design of an Application our company going to start to use shortly.
To tell the truth, no body in the IT favor the decision to use (purchase) this Application . ( but we can do nothing to prevent this from happening)
The application has a functionality to update this "Whole Sale Price" through GUI Item by Item. I am assigned to batch update them before go live.
Any idea how to do this ? ( Suppose the prices are below $255
)Thanks
-
create table #Tbl1
( ItemNo char(6), ItemInfo char(60) )
Insert into #Tbl1 (ItemNo, ItemInfo) Values('3000','FL 01 0 Ý 00000000 00V ')
Insert into #Tbl1 (ItemNo, ItemInfo) Values('3001','FL 01 0 Ý 00000000 00V ')
Insert into #Tbl1 (ItemNo, ItemInfo) Values('3002','FL 01 0 Ý 00000000 00V ')
select cast(ltrim(substring(ItemInfo,30,4))as varbinary) Bin, cast(cast(ltrim(substring(ItemInfo,30,4))as varbinary) as bigint) As TheInt
from #Tbl1
where ItemNo in( '3000', '3001')
select * from #Tbl1 where ItemNo in( '3000', '3001')
Update #Tbl1 Set ItemInfo = substring(ItemInfo,1,29)
+ ' '+ cast(cast(1875 as binary(2))as char(2)) --cast(cast(1875 as varbinary)as char(2))
+ substring(ItemInfo,34,26)
where ItemNo ='3001'
select cast(ltrim(substring(ItemInfo,30,4))as varbinary) Bin, cast(cast(ltrim(substring(ItemInfo,30,4))as varbinary) as bigint) As TheInt
from #Tbl1
where ItemNo in( '3000', '3001')
Couple of Things though:
As already stated is a flawed design but you probably can't do anything about it
The Length of the price string is variable so how do you know where the decimal point goes?
and Just for the fun of it I showed you an example that will get you going, just keep in mind that you will have to acount for the length and the decimal point somehow
HTH
* Noel -
Thank you Noel. Your way works perfect !
Yes, I have to take care decimal point.
But I can do nothing on its design.
This place is really a tresure of anyone lives on SQL. I visit here almost daily.
Thank you again.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply