February 2009 - Posts

SQL_VARIANT datatype and behaviour changes

When you use sql_variant datatype in sql server 2000, and add data to a table
using union all, it will work though datatype of data differs from
each other

declare @test table(variant_column sql_variant)
insert into @test
select 'test' union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

But in sql server 2005, it will throw error for datatype mismatch

The proper way of doing this is to explicitely cast any one of the values to be of sql_variant datatype

declare @test table(variant_column sql_variant)
insert into @test
select cast('test' as sql_variant) union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

This is one of the behavioural changes in sql server 2005

Also refer
Behaviour Changes in SQL Server 2005

Posted by Madhivanan with 1 comment(s)
Filed under: , ,

Find Alphanumerals only

Sometimes when you import data from other system, you may need to clean the data by removing unwanted data. Consider you import data where you want to keep only those which are purely alphanumerals. Here is a way to find out

declare @table table(data varchar(20))

Insert into @table
select '23498' as data union all
select 'tetst' union all
select 'kj&98' union all
select 'ad912'

select data from @table
where (data not like '%[^0-9a-z]%') and data like '%[a-z]%' and data like '%[0-9]%'

 

 

Posted by Madhivanan with no comments