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.........
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))
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?
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 "_".
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
@Phil, No need to apologise - after all, your blog has provided me with much enjoyment and quite a bit to think about!
