get next value - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

get next value

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-05-01 : 13:27:08

I have a field called Batch_No. with values like HOU-014583 and CAR-012334
I need to be able to select the last value with the first 3 of HOU and get the last number, then add one. So I need to make a value like HOU-014584

Dave
Helixpoint Web Development
http://www.helixpoint.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 13:37:01

You can do it like this (assuming your column name is x). However, something about this makes me uneasy, and leads me to believe that there may be opportunities to improve the design, if you have the ability to do so.

SELECT
	'HOU-'+RIGHT(CAST(MAX(CAST(RIGHT(x,6) AS INT)) + 1 AS VARCHAR(32)),6)
FROM
	YourTable
WHERE 
	x LIKE 'HOU-%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 14:23:02

this might be a good read
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-01 : 14:27:05

SELECT LEFT(batch_no,LEN(batch_no) - 1) +CAST(right(batch_no,1) +1 AS varchar)
FROM tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 14:29:26

quote:
Originally posted by shilpash

SELECT LEFT(batch_no,LEN(batch_no) - 1) +CAST(right(batch_no,1) +1 AS varchar)
FROM tablename

wont work always

what if number was

HOU-014579?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-01 : 15:24:02

CREATE TABLE #abc
(batch_no VARCHAR(20))

INSERT INTO #abc
(batch_no)
VALUES ('HOU-014579' -- batch_no - varchar(20)
)

SELECT LEFT(batch_no,LEN(batch_no) - 1) +CAST(right(batch_no,1) +1 AS varchar)
FROM #abc

This is what I got

(No column name)
HOU-0145710

Should not be this as a result.Please advise

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 15:32:41

quote:
Originally posted by shilpash

CREATE TABLE #abc
(batch_no VARCHAR(20))

INSERT INTO #abc
(batch_no)
VALUES ('HOU-014579' -- batch_no - varchar(20)
)

SELECT LEFT(batch_no,LEN(batch_no) - 1) +CAST(right(batch_no,1) +1 AS varchar)
FROM #abc

This is what I got

(No column name)
HOU-0145710

Should not be this as a result.Please advise

I think the OP is trying keep the length of the string the same and get the next number - so it would be HOU-014580

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-01 : 16:04:30

In that case,,

SELECT LEFT(batch_no,LEN(batch_no) - 1) + CAST(CASE WHEN LEN (RIGHT(batch_no,1) + 1 )>1 THEN (RIGHT(batch_no,2) + 1 ) ELSE (RIGHT(batch_no,1) + 1 ) END AS VARCHAR)
FROM #abc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 18:08:56

quote:
Originally posted by shilpash

In that case,,

SELECT LEFT(batch_no,LEN(batch_no) - 1) + CAST(CASE WHEN LEN (RIGHT(batch_no,1) + 1 )>1 THEN (RIGHT(batch_no,2) + 1 ) ELSE (RIGHT(batch_no,1) + 1 ) END AS VARCHAR)
FROM #abc

Wouldn't that be deferring the same issue to the 100th place? Fo r example, if the input value was HOU-014699. I think it might be easier if we convert it to a number and added 1. Of course, if we do that, what happens if the input is HOU-999999 is left unanswered.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-02 : 06:41:45

quote:
Originally posted by sunitabeck

quote:
Originally posted by shilpash

In that case,,

SELECT LEFT(batch_no,LEN(batch_no) - 1) + CAST(CASE WHEN LEN (RIGHT(batch_no,1) + 1 )>1 THEN (RIGHT(batch_no,2) + 1 ) ELSE (RIGHT(batch_no,1) + 1 ) END AS VARCHAR)
FROM #abc
Wouldn't that be deferring the same issue to the 100th place? Fo r example, if the input value was HOU-014699. I think it might be easier if we convert it to a number and added 1. Of course, if we do that, what happens if the input is HOU-999999 is left unanswered.

And that brings us back to your point of changing the design.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-02 : 08:11:52

quote:
And that brings us back to your point of changing the design.

Haha, I knew I was on to something, although I couldn't put my finger on it.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-02 : 13:29:49

[code]
drop table #abc
CREATE TABLE #abc
(batch_no VARCHAR(100))

INSERT INTO #abc
(batch_no)
select 'HOU-014579' -- batch_no - varchar(20)
Union all
select 'HOU-999999' -- batch_no - varchar(20)
Union all
select 'HOU-9999999' -- batch_no - varchar(20)
Union all
select 'HOU-000009' -- batch_no - varchar(20)
Union all
select 'HOU-003' -- batch_no - varchar(20)
Union all
select 'HOU-1023213553423423432' -- batch_no - varchar(20)

SELECT
Batch_no as ORGINALBATCHNO,
left(batch_no,CHARINDEX('-',batch_no)) as PRE_BATCH,
cast(right(batch_no,len(batch_no) - CHARINDEX('-',batch_no))as bigint) + 1 as NUMBERINCREASED
--left(batch_no,CHARINDEX('-',batch_no)) + cast(right(batch_no,len(batch_no) - CHARINDEX('-',batch_no)) + 1 as varchar(100))
,case when len(cast(cast(right(batch_no,len(batch_no) - CHARINDEX('-',batch_no)) as bigint) + 1 as varchar(50))) < 6 then left(batch_no,CHARINDEX('-',batch_no)) + right('000000' + cast(cast(right(batch_no,len(batch_no) - CHARINDEX('-',batch_no)) as bigint) + 1 as varchar(100)),6)
else left(batch_no,CHARINDEX('-',batch_no)) + cast(cast(right(batch_no,len(batch_no) - CHARINDEX('-',batch_no)) as bigint) + 1 as varchar(100))
end as NewWithMinumumDigits
FROM #abc

/*RESULTS
ORGINALBATCHNO PRE_BATCH NUMBERINCREASED NewnumberwithMinumumDigits
HOU-014579 HOU- 14580 HOU-014580
HOU-999999 HOU- 1000000 HOU-1000000
HOU-9999999 HOU- 10000000 HOU-10000000
HOU-000009 HOU- 10 HOU-000010
HOU-003 HOU- 4 HOU-000004
HOU-1023213553423423432 HOU- 1023213553423423433 HOU-1023213553423423433
*/

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources