Posts

Showing posts from May, 2009

T-SQL To Identify All Characters in a Column

When doing conversions, I often need to write a RegEx pattern for a column. This little query gets all the characters in a string. declare @ColName varchar (200) = 'Vin' declare @TblName varchar (200) = 'Units' declare @ sql varchar ( max ) declare @maxlength int declare @iterator int = 1 select @maxlength = c.CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS c where COLUMN_NAME = @ColName create table #allchars ( colchar CHAR (1) ) while @iterator < @maxlength begin set @ sql = 'insert into #allchars select distinct SUBSTRING(' + @ColName + ', ' + CAST (@iterator as varchar ) + ', 1) from ' + @TblName exec (@ sql ) set @iterator = @iterator + 1 end select distinct colchar from #allchars order by colchar drop table #allchars It’s not optimzed cause it just does what I want. Enjoy!