Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
S.No -> 1
Agent_code -> 1351
Branch -> BG
palmtec_id -> BG10
min_rec -> BG10000058
max_rec -> BG10000068
Last_billed -> 16-02-2016

I want to subtract 68-58 and store in third column

for ex: 68-58 =10

What I have tried:

I had tried (max_rec - min_rec) nut it doesnt subtract varchar there a way ti split the values and subtract ...can anyone help me out..thanks in advance
Updated 14-Mar-16 21:19pm
CHill60 14-Mar-16 9:56am    
Instead of storing palmtec_id as part of min_rec and max_rec just do that bit in your presentation layer. Min_rec and max_rec then become integers and your subtraction becomes trivial.
By normalising your table you will also save on storage

SELECT CAST(REPLACE(max_rec, BRANCH, '') as INT) - CAST(REPLACE(min_rec, BRANCH, '') as INT)
Share this answer
select CAST(Right(@max_rec ,2) as int) - CAST(Right(min_rec ,2) as int)
Share this answer
Herman<T>.Instance 14-Mar-16 6:25am    
What if the 2 digit must represent a number > 100?
AnantPithadiya 14-Mar-16 6:29am    
select CAST(right(max_rec ,8) as int) - CAST(Right(min_rec ,8) as int)
Herman<T>.Instance 14-Mar-16 8:07am    
NOPE. See my solution. BG10 in branchcode. I take that out.Remainder is INT.
alia007 15-Mar-16 3:53am    
That works perfectly!!! Thanks a lot
Try this
SELECT (RIGHT(max_rec,2) - RIGHT(min_rec,2)) FROM table_name;
Share this answer
Herman<T>.Instance 14-Mar-16 6:26am    
What if the 2 digit must represent a number > 100?
alia007 15-Mar-16 3:07am    
SELECT (cast(RIGHT(max(Receipt_number),4) as integer)- cast(RIGHT(min(Receipt_number),4) as integer))
because I had split Receipt_number column as two columns min_rec and max_rec...henceforth this worked out

I got exact result as expected after using ths...
alia007 15-Mar-16 3:20am    
thanks for all.. who helped me out !!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900