Member Avatar for divyakrishnan

Hi..

I want to take the max value of the substring of a varchar field having values as SN1,SN2....SN10.I used the query as following

select max(substring(st_no,3,4)) from tbl_st;

I am getting the max value as 9.I want to get 10.

If somebody knows please help me

  • 2 Contributors
  • 1 Reply
  • 111 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Momerath
Member Avatar for Momerath

Since they are strings, 9 comes after 1. You'll have to convert them to numbers if you want numerical sort order ( cast(substring(st_no,3,4) AS INT) ), assuming they are all numbers. Since they also have string part, you'll have to split them into two fields ordering first on the text, then on the numeric part.

Edited by Momerath because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.