How to get max value from a varchar field in MS SQL

Here I am going to explain you how to get max value from a varchar field in MS SQL.

Below is the table we are going to use here,

CREATE TABLE [Customer]

(

    [CustomerID] [nvarchar](50) NOT NULL,

    [CustomerName] [nchar](10) NULL,

    [Address] [nvarchar](50) NULL,

  CONSTRAINT [pk_Cust_ID] PRIMARY KEY CLUSTERED 

  (

    [CustomerID] 

  )

)

GO

If you look at the table I have declared CustomerID as nvarchar data type which means we will have both numeric and alphanumeric values.

Now we will insert some sample data,

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('1','Name1','Address1')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('2','Name2','Address2')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('11','Name11','Address11')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('2a','Name2a','Address2a')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('1a','Name1a','Address1a')

INSERT INTO [Customer] ([CustomerID] ,[CustomerName],[Address]) VALUES ('21','Name21','Address21')

GO

In the above statement 21 is the highest value.

SELECT MAX(CAST(CustomerID AS Int)) as [Customer ID] FROM Customers

WHERE ISNUMERIC(CustomerID)=1  AND CustomerID LIKE '%[0-9]%'

When we execute above query all alphanumeric values in the field  will be ignored and the output will be 21.