SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Statement to Truncate Numbers		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aharvestofhealth
Yak Posting Veteran

USA
52 Posts
Posted - 01/29/2013 :  19:18:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 01/29/2013 :  19:53:13  Show Profile  Reply with Quote
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
Edited by - jimf on 01/29/2013 20:00:57
Go to Top of Page

aharvestofhealth
Yak Posting Veteran

USA
52 Posts
Posted - 01/31/2013 :  09:22:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 01/31/2013 :  11:11:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000