How to find the highest numbered version of text?-CodePudding

How to find the highest numbered version of text? For example I have the data with text digit: Supra1, Supra2,...,SupraN in column1: translated_description.

select * from oe.product_descriptions where translated_description like '%Supra%';

I need to extract the value from another column (column2) for the highest number e.g. N=30 for Supra30 in column1.

CodePudding user response：

If all of the values in column1 have numbers with the same number of digits, you can order by it and use the fetch first syntax:

SELECT   column2
FROM     mytable
WHERE    column1 LIKE 'Supra%'
ORDER BY column1 DESC
FETCH FIRST ROW ONLY

If the number of digits in column1 varies, you'll have to extract them, convert the number, and sort numerically:

SELECT   column2
FROM     mytable
WHERE    column1 LIKE 'Supra%'
ORDER BY TO_NUMBER(REPLACE(column1, 'Supra', '')) DESC
FETCH FIRST ROW ONLY

CodePudding user response：

if you use SQL SERVER ,you can try this Query:

select ID as Column1,CONVERT(INT,SUBSTRING(ID,6, (LEN(ID)-5))) as Column2 
from T
order by  CONVERT(INT,SUBSTRING(ID,6, (LEN(ID)-5))) desc

CodePudding user response：

Try using regexp_substr to extract the number and then apply max on it:

SELECT max(to_number(regexp_substr(t.translated_description, 'Supra([0-9] )', 1, 1, NULL, 1))))
FROM oe.product_descriptions t

This will extract the number, assuming that the format of the content of the column is SOMETEXTnumber