How to find MIN and MAX of int values as substrings in SQL?

user_8991986 0
Viewed 1097

For example, I have the following table:

  pc   |   cd
---------------
  pc0  |   4x
  pc1  |   24x
  pc2  |   8x
  pc3  |   4x
  pc4  |   24x

I need to get something like this:

 cd_max
--------
   24x

or get it sorted:

  pc   |   cd
---------------
  pc0  |   4x
  pc3  |   4x
  pc2  |   8x
  pc1  |   24x
  pc4  |   24x

'24x' is obviously string but I need to get maximum/minimum of integers inside it.

I'm using MS SQL Server.

max min substring sql-server sql
4 Answers

If it's OK to assume the string will always just end in x, I'd cut it off, convert the string to a number, find the maximum and slap the x back on:

SELECT MAX(CAST(LEFT(cd, LEN(cd) - 1) AS INT)) + 'x'
FROM   mytable
user_24227760

You can try to replece 'x' only keep the int. the compare or get max.

SELECT CONCAT(MAX(CAST(REPLACE(cd,'x','') as int)) , 'x') cd_max
FROM T

or

SELECT *
FROM T
ORDER BY CAST(REPLACE(cd,'x','') AS INT)
user_51760710

Slice of the trailing x and cast the varchar to an int as follows:

cast(left(cd, len(cd) - 1) as int)

Now you can order by this value and pick the largest:

select top 1 cd as cd_max
from my_table
order by cast(left(cd, len(cd) - 1) as int) desc
user_3156890

schema:

create table Detail (pc varchar(100) ,cd varchar(100) );
insert into Detail values ('pc0','4x');
insert into Detail values ('pc1','24x');
insert into Detail values ('pc2','8x');
insert into Detail values ('pc3','4x');
insert into Detail values ('pc4','24x');

sql: I assume that only last char isn't digit 

  select * from Detail order by cast(left(cd,len(cd)-1) as int)

output:

pc  cd
pc0 4x
pc3 4x
pc2 8x
pc4 24x
pc1 24x

sql2: to get max cd

select top(1) cd as cd_max from Detail order by cast(left(cd,len(cd)-1) as int) desc

output:

cd_max
24x
user_80102750
Related