Finding whitespace in a specific column

-- This should check for the existence of any ascii values of 0 to 32
-- (33 for regular SPACE) and returns the rows. You can easily return
-- a count(*) instead if you want.
create procedure findwhitespace
@tablename sysname,
@colname sysname
as
declare @query varchar(1000)

select @query =
'
declare @whitespace varchar(128),
@ctr int

select @ctr = 0
select @whitespace = ''%[''
while @ctr < 32 -- for now, keep regular SPACE out of check, change to 33 if you want to check for it
begin
select @whitespace = @whitespace + char(@ctr)
select @ctr = @ctr + 1
end
select @whitespace = @whitespace + '']%''

select * from ' +@tablename+ ' where ' +@colname+ ' like @whitespace
'
exec(@query)
go

-- Procedure created, now do a quick test.
create table testtable(testval varchar(10))

insert testtable values('value1')
insert testtable values('value2')
insert testtable values('value3')
update testtable
set testval = testval + char(1)
where testval = 'value2'
update testtable
set testval = testval + ' '
where testval = 'value3'

exec findwhitespace @tablename = 'testtable', @colname = 'testval'

drop table testtable