CODEWITHSUNDEEP
sqlsql-server
Ras Rass
Ras Rass

Reputation: 225

How to get max value from string value in sql

I have a data like this. 

1000/PST-FDI/XII/2016
999/PST-TGR/XII/2016
998/PST-TGL/XII/2016
997/PST-SPM/XII/2016
996/PST-SPG/XII/2016
995/PST-SMG/XII/2016
994/PST-SMD/XII/2016
993/PST-SLT/XII/2016

The format value of the serial no is "SequenceNo/BranchCode/Month/Year". I want to select the last sequence no on this year. I made already select top 1 * from table order by serialNo desc, but it show me 999/PST-TGR/XII/2016. I don't know why.

I try to make a function like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[getFirstNumeric](
    @s VARCHAR(50)
)  
RETURNS int AS 
BEGIN

set @s = substring(@s,patindex('%[0-9]%',@s),len(@s)-patindex('%[0-9]%',@s) + 1) 
if patindex('%[^0-9]%',@s) = 0
    return @s
set @s = substring(@s,1,patindex('%[^0-9]%',@s)-1) 

return cast(@s as int)
end

also it show for me 999/PST-TGR/XII/2016. Or because I made DESC so it comes late latest word 'PST-TGR' ?? so how to noticed just the sequence no based on the current year ?

Upvotes: 0

Views: 2480

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Try This.

Using Substring :

             select * from ( select * ,
             cast(left(  
             (substring(Colname, patindex('%[0-9]%', Colname), 
             len(Colname))), patindex('%[^0-9]%',   
             (substring(Colname, patindex('%[0-9]%', Colname), len(Colname))) ) - 1)as int)  as serailID
              from Tablename
             )a      
              order by serailID desc

Check Demo .

Using Function : 

            SET ANSI_NULLS ON
            GO
            SET QUOTED_IDENTIFIER ON
            GO
            create FUNCTION [dbo].[getFirstNumeric](
                @s VARCHAR(50)
            )  
            RETURNS int AS 
            BEGIN

            set @s =      left(  
                 (substring(@s, patindex('%[0-9]%', @s), 
                 len(@s))), patindex('%[^0-9]%',   
                 (substring(@s, patindex('%[0-9]%', @s), len(@s))) ) - 1)

            if patindex('%[^0-9]%',@s) = 0
                return @s
            set @s = substring(@s,1,patindex('%[^0-9]%',@s)-1) 

            return cast(@s as int)
            end

            select *, dbo.getFirstNumeric(columnname) as serailno1
            from TableName
            order by dbo.getFirstNumeric(serailno1) desc

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Because string ordering is hapening where 999 comes before 1000 in descending order

Try this 

Order by cast(left(col,charindex('/',col)-1) as int) desc --,cast(right(col,4)  as int) desc

LEFT/RIGHT return type is Varchar/Nvarchar based on input. So we need to do a explicit conversion to INT to get the ordering right

Upvotes: 1

odlan yer
odlan yer

Reputation: 771

Try This

SELECT top 1
   [YourColumn]
 FROM [YourTable]
order by 
left([YourColumn], charindex('/', [YourColumn]) - 1)  asc

Upvotes: 0

Related Questions