SQL Server Finding String Length


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)

Leave a comment

Your email address will not be published. Required fields are marked *