MAX with SUBSTRING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MAX with SUBSTRING

Author

Topic

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-07 : 21:38:53

I have a column where I have to split a value starting with P
and want to find the maximum number out of the result.
Example I have data like:

Paa00001,Pab00002,paa00005,pqa00012
I want result as "00012" the maximum integral value. and then add one to it with a constant prefix PXX
So in this case final expected result would be : "PXX00013"

I have used the following code and getting nothing:

SELECT MAX(SUBSTRING (id, 4,9) as interger ) as Maxvalue
FROM my_table
WHERE id like 'P%'

Please help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-07 : 22:54:23

[code]
SELECT '00000' + convert(varchar(5), max(SUBSTRING (id, 4, 5)) + 1) as Maxvalue
FROM my_table
WHERE id like 'P%'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sshelper
Posting Yak Master

216 Posts

Posted - 2011-07-08 : 00:57:29

Try this one also:

SELECT 'PXX' + RIGHT('00000' + CAST(MAX(CAST(SUBSTRING([ID], 4, 5) AS INT)) + 1 AS VARCHAR(5)), 5)
FROM my_table
WHERE [ID] LIKE 'P%'

Regards,
SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources