CODEWITHSUNDEEP
sql-servert-sql
bautista
bautista

Reputation: 794

Get substring, cast it to int, increment it and concat it

select 
    CONCAT('~', (cast (ISNULL((select substring(max(NR), 2, len(max(NR)) - 1)   
                               from XY 
                               where left(NR, 1) = '~'), 0) AS int) + 1))

What this should achieve is getting the max number of the column NR which is prepended with a ~.

This seems to work fine for numbers up to 10. But once I have an entry in XY where NR is ~10 it always returns the same result and I can't seem to figure out why. So here is the breakdown of the statement in my opinion:

From inner to outer:

First i get the substring in the length of the maximum number which is prepended with a tilde (solely the number) of NR starting at position 2 in order to ignore the tilde.

Then I increment the number and handle it as a string.

Finally I prepend a tilde and concat it which should. This should achieve the desired output in my opinion.

Table XY:

ID NR
11 ~1
12 ~2
13 ~3
14 ~4
15 ~5
16 ~6
17 ~7
18 ~8
17 ~9
18 ~10

Desired output for Select statement with above table:

~11

Actual output:

~10

TLDR; if there are entries in NR prepended with a tilde (i can assure that always an integer trails after the tilde):

I want the select statement to return the max number of NR which is prepended with a tilde +1

If there are no entries with a tilde in NR -> return ~1

Upvotes: 0

Views: 221

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

From what you describe, NR is a string. Although you are casting to an int, it would seem something is going wrong. I would suggest simplifying the query to something like this:

select concat('~',
              coalesce(max(cast(stuff(nr, 1, 1, '') as int)) + 1, 1)
             ), stuff(max(nr), 1, 1, ''), max(nr)
from XY
where nr like '~%';

Note that storing an integer encoded in a string is usually a bad idea. Why not just store an integer, using an identity() column or a sequence.

Here is a db<>fiddle.

Upvotes: 1

Related Questions