# question

## How to find max sequence number in a string

i am having a data in one of the column like 'A10000001_XXXX' from this data i want to find the max number ...here 'A10' after that number and till '_XXXX' those numbers need to generate based on max number+1 Any help is highly appreciatable.........

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sorry, which number you need ? the XXXX part or the "000001" after A10 ?
0 Likes 0 ·
after 'A10' remaing no like from '000001' till here from this no i need max no
0 Likes 0 ·

·
declare @str varchar(20) select @str = 'A10000001_XXXX' select stuff(@str, 4, 6, right(replicate('0', 6) + convert(varchar(10), convert(int, substring(@str, 4, 6)) + 1), 6))

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

as per the requirement for every transaction i need to fetch the max no and for that i need to increment by 1 and that value i need to bind with the remaining format ...that value i need to insert into the table... will this work for the requirment what i am expecting?
0 Likes 0 ·
yes. Give it a try. The query will add 1 to the number and return A10000002_XXXX
0 Likes 0 ·
Try this for size for finding the maximum number: DECLARE @tt TABLE (FindMaxInThis VARCHAR(20)) INSERT INTO @tt SELECT 'A10000001_XXXX' UNION ALL SELECT 'A10314159_xyzzy' UNION ALL SELECT 'A10999_Emergency' Up to here, we're just setting up some test data. The real select statement is below. Change "FindMaxInThis" to your column name, and @tt to your table name. SELECT MAX(CONVERT(INT,SUBSTRING(FindMaxInThis,4,CHARINDEX('_', FindMaxInThis, 0)-4))) FROM @tt It'll do what you want with the added bonus of not worrying about how many digits there are between the "A01" and the "_".

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.