Compare one column values for the table in sql

Question

I have table with X number of columns. One of them is nvarchar(50). Values of this column are like this:

13-46187(IC)
13-46186(IC)
13-46189
13-46185
13-46184

I want to extract/find the highest number that the column value ends with (in this case 189). How do I accomplish that?

can you use substring to extract the values? – AJP Jul 12 '13 at 22:42 — AJP, Jul 12 '13 at 22:42

score 1 · Accepted Answer · answered Jul 12 '13 at 23:05

This is hardcoded stuff. but will give you some ideas..

create table #temp
(
    textfield varchar(50)
)

insert into #temp
select '13-46187(IC)'
UNION
select '13-46186(IC)'
UNION
select '13-46189'
UNION
select '13-46185'
UNION
select '13-46184'


select Max(Convert(int,substring(SUBSTRING(textfield, 6, LEN(textfield)), 1, 3)))
from #temp

Compare one column values for the table in sql

1 Answers1