question

Murali avatar image
Murali asked

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.........
t-sql
2 comments
10 |1200

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 ·
Squirrel avatar image
Squirrel answered
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))
2 comments
10 |1200

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 ·
ThomasRushton avatar image
ThomasRushton answered
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 "_".
10 |1200

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

Phil Factor avatar image
Phil Factor answered
Thomas, sorry for stealing your test data. I added a nasty string without an embedded number. Here is a more generic way of taking the first embedded number from a string. If you need a decimal point in there as well or a pound sign, ythen it is slightly more complicated but the principle is the same. DECLARE @tt TABLE ( FindMaxInThis VARCHAR(20) ) INSERT INTO @tt SELECT 'A10000001_XXXX' UNION ALL SELECT 'A10314159_xyzzy' UNION ALL SELECT 'A10999_Emergency' UNION ALL SELECT 'Evil' SELECT SUBSTRING(topped, 1, PATINDEX('%[^0-9]%', topped + 'X') - 1) FROM ( SELECT SUBSTRING(FindMaxInThis, PATINDEX('%[0-9]%', FindMaxInThis + '0'), 100) AS topped FROM @tt ) toppedString
1 comment
10 |1200

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

@Phil, No need to apologise - after all, your blog has provided me with much enjoyment and quite a bit to think about!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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