Technical Article

Finding whitespace in a specific column

,

After reading entries regarding whitespace (http://www.sqlservercentral.com/articles/T-SQL/63953/), I was wondering if there was a quick way to check for any white space in a particular column? These are the ascii equivalents of 0-32 (33 if you want to include a regular SPACE). This procedure simply takes a table and column name and returns the rows (but you can change to return count(*) if you think it will be a lot)

-- 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

Rate

3.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.2 (5)

You rated this post out of 5. Change rating