Suppose you have a table with a column that needs some data validation performed on it. The data will be sent to another system that requires that the string length does not exceed 30 characters. We need to tell the user of the routine how many rows violate the condition as well as displaying those rows to the user.

Suppose our table name is called [NameLength]. For simplicity in this example, the table only has one column called [personname]. Here is our script, followed by the output when we send everything out as text.

select [personname] from [NameLength]
declare @NumViolatingRows int = 0;
select @NumViolatingRows = count(*) from [NameLength] where len([personname]) > 30;
print 'Number of rows where data in column [personname] exceeds 30 characters: ' + cast(@NumViolatingRows as varchar(5))
select [personname] from [NameLength] where len([personname]) > 30;

Here is the SSMS output.

personname
--------------------------------------------------
mile
john
this has more than thirty characters

(3 rows affected)

Number of rows where data in column [personname] exceeds 30 characters: 1
personname
--------------------------------------------------
this has more than thirty characters

(1 row affected)

Maximum Length of a Column

Here is a script you can run in a non-production database.

CREATE TABLE [dbo].[Names](
	[Name] [varchar](50) NULL,
	[Number] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Names] ([Name],[Number]) VALUES ('Joe', 23)
INSERT INTO [dbo].[Names] ([Name],[Number]) VALUES('Sally Susan Smith', 5)
INSERT INTO [dbo].[Names] ([Name],[Number]) VALUES  ('Jackie', 33)
GO

SELECT MAX(LEN(Name)) As 'Max Length of Name'
FROM [dbo].[Names]

Here is another way. Our table is called Names.

SELECT TOP 1 Name, Number, LEN(Name) AS L
FROM Names
ORDER BY L DESC