December 2009 - Posts

Happy Holidays

In the Query Analyser, set the Result mode to Text (Press CTRL+T) and run the following code

set nocount on

select
        space(17-len(replicate(char(94),no)))+ replicate(char(94),no*2-1)
from 
        (
                select top 10 row_number() over (order by name) as no from sysobjects 
        )
as t
union all
select
space(14)+replicate(char(124),5)
union all
select
space(10)+cast(0x486170707920486F6C6964617973 as varchar(100))

Posted by Madhivanan with no comments

Search a value in character column of all tables

Sometimes, you may need to find a value in the characters columns (char, varchar, etc) in all the tables

This is one of the methods to do it

declare @sql varchar(max),@search varchar(100)

set @sql=''

set @search='your search string'

select
        @sql
=@sql+'select '''+c.table_name+''' as table_name,'''+c.column_name+''' as column_name,['+c.column_name+'] as column_value from '+c.table_name+' where ['+c.column_name+'] like N''%'+@search+'%'' union all '
from
       
information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name
where
        t
.table_type='BASE TABLE' and data_type like '%char%'

set @sql=left(@sql,len(@sql)-10)

exec(@sql)

The resultset would have table_name, column_name and column_value

Posted by Madhivanan with no comments

Removing unwanted characters - Part 2


In my previous post about Removing unwanted characters , I posted a method that used a function

Here is another method that uses Dynamic SQL

declare @data table (data varchar(100))

-- table that has source data

insert @data
select 'tes^@&t %stri)-n!g' data union all
select
'))aaer***********)' union all
select
'&^&&hsdf()' union all
select
'jj&wk' union all
select
')hw*pa' union all
select
'No&@#$53*24,Mai()$n R--!oad'

-- table that has unwanted characters

declare @clean_chars table (char_id int identity(1,1),chars char(1))

insert into @clean_chars (chars)
select '^' as repl union all
select ')' union all
select
'(' union all
select
'&' union all
select
'*' union all
select
'%' union all
select
'@' union all
select
'-' union all
select
',' union all
select '$' union all
select
'#' union all
select
'!'

--T-sql code that removes them

declare @replace varchar(8000),@chars varchar(8000),@sql varchar(8000)

select @chars='',@replace='',@sql=''

select @replace = @replace+'replace(', @chars = @chars+''''+chars+''',''''),' from @clean_chars

set @replace=@replace+'''~!@#'','

set @replace=@replace+substring(@chars,1,LEN(@chars)-1)

select @sql=@sql+'select '''+data+''' as old_data, '+replace(@replace,'~!@#',data)+' as new_data union all ' from @data

set @sql=substring(@sql ,1,LEN(@sql)-10)

exec(@sql)

 

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