CODEWITHSUNDEEP
sqlsql-server
user5651777
user5651777

Reputation:

Extract last number out of existing one

I have to extract the next number out of given numbers. My table contains numbers like below. The main product is always with .1 at the end and could or not contains his subproducts e.g:

07.0001.1 (main product)
07.0001.2 (his sub)
07.0001.3 (his sub)
etc..

01.1453.1
01.1453.2
03.3456.1
03.3456.2
03.3456.3
03.5436.1
03.5436.2
03.5436.3
03.5436.4
12.7839.1
12.7839.2
12.3232.1
12.4444.1
12.4444.2
13.7676.1

i want to pass first to digits of a number to the query and based on that get all which starts with that and then get the highest number out of next four and return this number + 1.

So if we would take above example inputs if i say 12 then it should find this product: 12.7839.x and return 12.7839 + 1 so 12.7840

Another example if i say 03 then should find 03.5436 so 03.5436 + 1 so should return 03.5437

Hope you know what i mean.

I am not so familiar with SQL but this is how far i am:

select * from tbArtikel where Nummer LIKE '12.%'

Upvotes: 0

Views: 76

Answers (3)

Gautam G
Gautam G

Reputation: 494

This is another alternate for achieving the desired results. Providing the option to pass number to be queried. Consider following SQL statements

CREATE TABLE tblDummyExample
(
    Number      VARCHAR(64)
)


INSERT INTO tblDummyExample 
VALUES ('07.0001.1')
, ('07.0001.2')
, ('07.0001.3')
, ('01.1453.1')
, ('01.1453.2')
, ('03.3456.1')
, ('03.3456.2')
, ('03.3456.3')
, ('03.5436.1')
, ('03.5436.2')
, ('03.5436.3')
, ('03.5436.4')
, ('12.7839.1')
, ('12.7839.2')
, ('12.3232.1')
, ('12.4444.1')
, ('12.4444.2')
, ('13.7676.1')


DECLARE @startWith VARCHAR(2) = '12'        -- provide any number as input

SELECT @startWith + '.'+ CAST((MAX(CAST(SUBSTRING(ex.Number, (CHARINDEX('.', ex.Number, 1) + 1), (CHARINDEX('.', ex.Number, (CHARINDEX('.', ex.Number, 1) + 1)) - (CHARINDEX('.', ex.Number, 1) + 1))) AS INT)) + 1) AS VARCHAR(16))
FROM tblDummyExample ex
WHERE ex.Number LIKE @startWith+'%'

I'm sure, this solution is not restricted to any specific SQL Server version.

Upvotes: 2

StackUser
StackUser

Reputation: 5398

Try like this,

DECLARE @table TABLE (col VARCHAR(10))

INSERT INTO @table
VALUES ('01.1453.1')
    ,('01.1453.2')
    ,('03.3456.1')
    ,('03.3456.2')
    ,('03.3456.3')
    ,('03.5436.1')
    ,('03.5436.2')
    ,('03.5436.3')
    ,('03.5436.4')
    ,('12.7839.1')
    ,('12.7839.2')
    ,('12.3232.1')
    ,('12.4444.1')
    ,('12.4444.2')
    ,('13.7676.1')

SELECT TOP 1 left(col, charindex('.', col, 1) - 1) + '.' + convert(VARCHAR(10), convert(INT, substring(col, charindex('.', col, 1) + 1, charindex('.', col, charindex('.', col, 1) + 1) - (charindex('.', col, 1) + 1))) + 1)
FROM @table
WHERE col LIKE '03.%'
ORDER BY 1 DESC

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

Try this, extract the first two parts, convert the 2nd to a numeric value, add one and convert back to a string again:

select
    parsename(max(nummer), 3) + '.'                    -- 03
    + ltrim(max(cast(parsename(nummer, 2) as int) +1)) -- 5436 -> 5437
    + '.1'                                          
from tbArtikel 
where Nummer LIKE '03.%'

Upvotes: 0

Related Questions