| Author |
 Topic  |
|
|
helixpoint
Constraint Violating Yak Guru
252 Posts |
 Posted - 05/01/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/01/2012 : 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
India
48041 Posts |
|
|
shilpash
Yak Posting Veteran
75 Posts |
Posted - 05/01/2012 : 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
India
48041 Posts |
Posted - 05/01/2012 : 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
Yak Posting Veteran
75 Posts |
Posted - 05/01/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/01/2012 : 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
Yak Posting Veteran
75 Posts |
Posted - 05/01/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/01/2012 : 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
Flowing Fount of Yak Knowledge
2113 Posts |
Posted - 05/02/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/02/2012 : 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
Flowing Fount of Yak Knowledge
USA
1203 Posts |
Posted - 05/02/2012 : 13:29:49
|
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
*/
|
Edited by - Vinnie881 on 05/02/2012 13:34:30 |
|
|
| |
Topic |
|
get next value
