I am trying to get the next value from this filed SU - 1 /2016
Query I used
SELECT RIGHT('000' + CAST(ISNULL(MAX(SUBSTRING(InvoiceNO,4, 1)), 0) + 1 AS VARCHAR(4)), 4)
from [dbo].[Invoice]
The query output is 0001, it should be 0002.
Add a comment
|
4 Answers
You substring this value SU - 1 /2016 from 4th position which gives you "-". So to get the 1 you need to start from 6th position which gives you expected output.
SELECT
RIGHT('000' + CAST(ISNULL(MAX(SUBSTRING(InvoiceNO,6, 1)), 0) + 1 AS VARCHAR(4)), 4)
from [dbo].[Invoice]
Comments
If I get you correctly, you are using the substring function on SU - 1 /2016. You take a substring from 4th position with a length of 1 (remember substring is 1 based, not 0). So you are getting -. If you try:
SELECT '-' + 1
Gives: 1.
You'll probably need to start 'substringing' at position 6.
Comments
use this code.
SELECT RIGHT('000' + CAST((ISNULL(MAX(SUBSTRING(exampleColumn,6, 1)), 0) + 1) AS VARCHAR(4)), 4)
from [dbo].tblExample
You have take a char from 4th position but your data "1" is at 6th position.
Comments
You can make your script a bit shorter and use this instead:
SELECT RIGHT(MAX(SUBSTRING(InvoiceNO,6, 1)) + 10001, 4)
-- Including this for test:
FROM (values('SU - 1 /2016')) x(InvoiceNO)
2 Comments
Mukesh Kalgude
SELECT RIGHT(MAX(SUBSTRING(InvoiceNO,6, 1)) + 10001, 4) -- Including this for test: FROM (values('SU - 0002 /2016')) x(InvoiceNO) given different out put
t-clausen.dk
@MukeshKalgude I tried, I am getting the same output as the other answers. Can you be more specific ?
