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.
Author |
Topic |
aharvestofhealth
Yak Posting Veteran
52 Posts |
Posted - 2013-01-29 : 19:18:54
|
When I run the following query:SELECT USR_ITEM_PROMOFROM PO_VEND_ITEMWHERE USR_ITEM_PROMO IS NOT NULLI 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_NOSo maybe something like this?update IM_ITEMset 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 nullAny 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 TheNumberJimEveryday I learn something that somebody else already knew |
|
|
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! |
|
|
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_ITEMset 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 |
|
|
|
|
|
|
|