Get MAX value in VARCHAR(900) column

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

I have a table in which I am storing ID as VARCHAR(900) this is a primary key column and not using IDENTITY AUTOINCREMENT

I have created the ID as IE-1-1
It goes on like IE-1-85000 until it reaches the upper limit in both bigint numbers
Now I have around 85000 rows in it
BUT it still shows IE-1-9 as the max value when I query it like this

SQL

DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))

INSERT INTO @TempICFEventID VALUES ('IE-1-1')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-2')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-3')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-4')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-5')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-6')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-7')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-8')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-9')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-10')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-11')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-12')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-13')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-14')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-15')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-16')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-17')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-18')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-84999')
SELECT MAX(SplitValue) FROM @TempICFEventID 
INSERT INTO @TempICFEventID VALUES ('IE-1-85000')
SELECT MAX(SplitValue) FROM @TempICFEventID

Why is this happening

What I have tried:

This is my current logic to generate the ID

SQL

DECLARE @Current VARCHAR(900)

DECLARE @Temp1 BIGINT
DECLARE @Temp2 BIGINT

DECLARE @Upper BIGINT
DECLARE @Middle SMALLINT
DECLARE @Lower BIGINT

SET @Lower = -9223372036854775808
SET @Middle = 0
SET @Upper = 9223372036854775807

SELECT @Current = MAX(Ref_ICFEvent_Id) FROM CaseMatrix_ICFEvent
PRINT @Current

DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))

IF(@Current IS NOT NULL)
BEGIN
 INSERT INTO @TempICFEventID(SplitValue)
 SELECT * FROM CaseMatrix_Split(@Current,'-')
 SELECT * FROM @TempICFEventID
 SELECT @Temp1 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 2
 SELECT @Temp2 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 3

 IF(@Temp1 IS NOT NULL AND @Temp2 IS NOT NULL)
 BEGIN
     IF(@Temp1 <> @Upper)
     BEGIN
         IF(@Temp2 <> @Upper)
         BEGIN
             SET @Temp2 = @Temp2 + 1
             SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' +  CONVERT(VARCHAR(900),@Temp2)
         END
         ELSE
         BEGIN
             SET @Temp1 = @Temp1 + 1
             IF(@Temp1 <> @Upper)
             BEGIN
                 SET @Temp2 = 1
                 SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' +  CONVERT(VARCHAR(900),@Temp2)
             END
             ELSE
             BEGIN
                 SET @Ref_ICFEvent_Id = 'MAXLIMIT'
             END
         END
     END
     ELSE
     BEGIN
         SET @Ref_ICFEvent_Id = 'MAXLIMIT'
     END
 END
END
ELSE
BEGIN
 SET @Ref_ICFEvent_Id = 'IE-1-1'
END

Posted 28-Jun-18 23:21pm

Christopher Fernandes

Updated 29-Jun-18 3:57am

Nelek

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2018-06-29T02:31:00

Solution 1

Follow the instruction provided by marc_s in this thread: sql server - How to autoincrement a varchar[^]

Posted 29-Jun-18 2:31am

Maciej Los

Richard Deeming · Answer 2 · 2018-06-29T03:58:00

Solution 2

IE-1-9 is the maximum value.

Remember, you're storing the values as strings, so you're using a string comparison.

Comparing two values, IE-1-9 and IE-1-85000:

'I' vs 'I': equal
'E' vs 'E': equal
'-' vs '-': equal
'1' vs '1': equal
'-' vs '-': equal
'9' vs '8': '9' is larger
Result: 'IE-1-9' is larger than 'IE-1-8...'

Posted 29-Jun-18 3:58am

Richard Deeming

Comments

Christopher Fernandes 29-Jun-18 10:16am

How about if I strip out the prefix IE and split the numbers & then compare the number by creating a custom function

Richard Deeming 29-Jun-18 10:22am

It might work, but the performance would be appalling.

Follow the instructions in the link Maciej gave you: store the two numbers in separate number fields, using a composite primary key. You can then either add a computed column for the full ID, or compute it in code when you display the records.

And as someone else mentioned recently, you're a long way off from needing to increment the first number if you're using bigint. The maximum bigint value is 9223372036854775808.

Dave Kreskowiak 29-Jun-18 10:58am

How about storing the pieces of the ID value in separate columns, then using a calculated column to build the entire ID from the separate values?

Christopher Fernandes 29-Jun-18 11:10am

Yes thats what I am planning to do now since it is so much affecting the perfomance