Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Statement to Truncate Numbers

Author  Topic 

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2013-01-29 : 19:18:54
When I run the following query:

SELECT USR_ITEM_PROMO
FROM PO_VEND_ITEM
WHERE USR_ITEM_PROMO IS NOT NULL

I get output similar to below:

Monthly Special 19% off per ea
Monthly Special: 20.00% off per ea
Monthly Special: 28.00% off per ea
NC Special 20% off per cs, $12.80
NC Special: 25% off per cs, $33.15
Monthly Special: 15.00% off per ea
Monthly Special 10.00% off per ea
Monthly Special: 30.00% off per cs

Now what I'm trying to do is run an update statement that can take the numbers before the % sign and copy them to the
IM_ITEM.PROF_COD_2 field. The difficult part though is that I only want the two digits (e.g. 20 not 20.00) that come before the % sign. To reiterate, I don't want the 0's between the . and the %.

There is a primary key for both tables that we can join on:

PO_VEND_ITEM.ITEM_NO = IM_ITEM.ITEM_NO

So maybe something like this?

update IM_ITEM
set IM_ITEM.PROF_COD_3 = PO_VEND.USR_ITEM_PROMO (I assume a nested select would go here to truncate to only the 2-digit number)
where PO_VEND.USR_ITEM_PROMO is not null

Any help would be appreciated!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-29 : 19:53:13
There's a better way to do this, but this is all I got. It is dependent on the first numeric being part of the %
DECLARE @str varchar(100) = 'Monthly Special: 20.00% off per ea '

SELECT PATINDEX('%[0-9]%',@str) as FirstNumber,PATINDEX('%[%]%',@str) as PercentSign
, ROUND( SUBSTRING(@str,PATINDEX('%[0-9]%',@str),PATINDEX('%[%]%',@str)-PATINDEX('%[0-9]%',@str) ),0) as TheNumber

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2013-01-31 : 09:22:38
Awesome! Thanks for you help.

Any advice on how to get this into my update statement? I don't have a lot of SQL experience, so any assistance is much appreciated!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 11:11:43
Like shown below. I am simply using the update statement you had posted and replacing the right side with Jim's code (in red)
update IM_ITEM
set IM_ITEM.PROF_COD_3 =
ROUND( SUBSTRING(USR_ITEM_PROMO,PATINDEX('%[0-9]%',USR_ITEM_PROMO),
PATINDEX('%[%]%',USR_ITEM_PROMO)-PATINDEX('%[0-9]%',USR_ITEM_PROMO) ),0)
where PO_VEND.USR_ITEM_PROMO is not null
Go to Top of Page
   

- Advertisement -