Tuesday, May 12, 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!

No comments: