Return incorrect numeric value

Question

Post reply

Return incorrect numeric value

luissantos

SSCertifiable

Points: 7391

March 23, 2021 at 3:21 pm

#3861271

Hello,

I have make this query to return the max numeric value from the right for adding +1 :

SELECT 
CAST(Isnull(MAX(REVERSE(SUBSTRING(reverse(Ltrim(Rtrim(cod))),0,CHARINDEX ('-',REVERSE(Ltrim(Rtrim(cod))))))),1) + 1 as varchar)[NextNumber]
From QNC (Nolock)
Where qnc.cod LIKE 'NCREC-CL'+'.'+'CMP' + '-'+'%'

--This Return : 10 that is incorrect, the correct value must be 11

--if i modify my query and Group by Code:

SELECT qnc.cod,
CAST(Isnull(MAX(REVERSE(SUBSTRING(reverse(Ltrim(Rtrim(cod))),0,CHARINDEX ('-',REVERSE(Ltrim(Rtrim(cod))))))),1) + 1 as varchar)[NextNumber]
--CAST(Isnull(MAX(right(Ltrim(Rtrim(cod)),1)),1) + 1 as varchar) [nr]
From QNC (Nolock)
Where qnc.cod LIKE 'NCREC-CL'+'.'+'CMP' + '-'+'%'
  group BY qnc.cod

/*
this return:
cod         NextNumber
NCREC-CL.CMP-10     11
NCREC-CL.CMP-5      6
NCREC-CL.CMP-6      7
NCREC-CL.CMP-7      8
NCREC-CL.CMP-8      9
NCREC-CL.CMP-9     10
*/

--the correct cod for the new record must be : NCREC-CL.CMP-11 , and not : NCREC-CL.CMP-10

Please someone could help me on this !?

Many thanks

Best regards,

Ls

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

DECLARE @cod_pattern varchar(50)
SET @cod_pattern = 'NCREC-CL'+'.'+'CMP'+'-'+'%'

;WITH cte_next_numbers AS (
    SELECT LEFT(cod, cod_base_length) AS cod_base,
        MAX(CAST(SUBSTRING(cod, cod_base_length + 1, 20) AS int)) AS cod_base_high_number
    FROM QNC
    CROSS APPLY (
        SELECT LEN(cod) - CHARINDEX('-', REVERSE(cod)) + 1 AS cod_base_length
    ) AS calc1
    WHERE cod LIKE @cod_pattern
    GROUP BY LEFT(cod, cod_base_length)
)
SELECT Q.cod, ISNULL(c.cod_base_high_number + 1, 1) AS next_number
FROM QNC Q
CROSS APPLY (
    SELECT LEN(Q.cod) - CHARINDEX('-', REVERSE(Q.cod)) + 1 AS cod_base_length
) AS calc1
LEFT OUTER JOIN cte_next_numbers c ON c.cod_base = LEFT(Q.cod, Q.cod_base_length)
WHERE Q.cod LIKE @cod_pattern

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hi Scott,

Many thanks for your reply, i made some changes to return only 1 value.

Can you explain to me in a very simple way why my query does not return the correct result! this makes me curious because in the first documents everything worked well, that is, they were well numbered.

Thanks,

Best regards,

Luis

ScottPletcher SSC Guru Points: 101096 More actions · Answer 3

I believe it's because you're doing "MAX" but the value is a string not a number. Therefore '9' is greater than '10', or '1', so '9' becomes the MAX value. When you add 1 to that, you get 10.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello Scott,

Thanks again for your explanation about my issue.

Best regards,

Luis