Friday, February 24, 2012

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.

No comments: