15,938,218 members
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 columns..is there a way ti split the values and subtract ...can anyone help me out..thanks in advance
Posted
Updated 14-Mar-16 21:19pm
v2
Comments
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

## Solution 2

SQL
`SELECT CAST(REPLACE(max_rec, BRANCH, '') as INT) - CAST(REPLACE(min_rec, BRANCH, '') as INT)`

## Solution 3

```select CAST(Right(@max_rec ,2) as int) - CAST(Right(min_rec ,2) as int)
```

Comments
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

## Solution 4

Try this
SELECT (RIGHT(max_rec,2) - RIGHT(min_rec,2)) FROM table_name;

Comments
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)

Top Experts
Last 24hrsThis month
 Pete O'Hanlon 75 merano99 20 Peter_in_2780 15 OriginalGriff 10 Kate Abdo 10
 OriginalGriff 730 Pete O'Hanlon 675 Richard Deeming 475 merano99 235 Dave Kreskowiak 190

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