Thursday, November 6, 2008

Finding whitespace in a specific column

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
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
select @whitespace = @whitespace + char(@ctr)
select @ctr = @ctr + 1
select @whitespace = @whitespace + '']%''

select * from ' +@tablename+ ' where ' +@colname+ ' like @whitespace

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

No comments: