MSQL SELECT MAX(sup_id) where data type is char(7) (Plz Help me Out))

[Moss ali]

i want to select MAX Id where i have the list database

s001.1
s001.2
s001.3
s001.4
s002.1
s002.2
s002.3
s002.4
upto so On

my field type is char(7)

MSQL SELECT MAX(sup_id) where data type is char(7)

plz help me out to perform this query in SQL.

[tjaank]

try this:

declare @temp TABLE
(this char(7))
INSERT INTO @temp VALUES('s001.1')
INSERT INTO @temp VALUES('s001.2')
INSERT INTO @temp VALUES('s001.3')
INSERT INTO @temp VALUES('s001.4')
INSERT INTO @temp VALUES('s002.2')
INSERT INTO @temp VALUES('s002.3')
INSERT INTO @temp VALUES('s003.3')

SELECT 's' + CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS VARCHAR)
FROM @temp

[Moss ali]

Thank you so much.

[Moss ali]

When it return max in this condition s003.3.Then i want to increment in S003.3 S003.4 and so on dynamically parametrically.Please give me suggestion how to achieve.Thanks

[tjaank]

i did'nt understand what do you want. but may be u want something like this:

declare @temp TABLE
(this char(7))
INSERT INTO @temp VALUES('s001.1')
INSERT INTO @temp VALUES('s001.2')
INSERT INTO @temp VALUES('s001.3')
INSERT INTO @temp VALUES('s001.4')
INSERT INTO @temp VALUES('s002.2')
INSERT INTO @temp VALUES('s002.3')
INSERT INTO @temp VALUES('s003.3')

SELECT 's00' + CAST((CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS DECIMAL(18,1)) + 0.1) AS VARCHAR)
--SELECT 's' + CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS VARCHAR)
FROM @temp