Moved my blog

For all those reading my blog, for your information, I have moved my blog to http://beyondrelational.com/blogs/madhivanan
Thanks to Simon for his support at sqlblogcasts.com

Posted by Madhivanan with no comments
Filed under: ,

Find alternate week day

My co-worker had a task of finding minimum alternate week day as of today for a given date and day

Consider these data

Date : 2009-12-22

day  : 5 (it denotes Thursday)

It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date).

The task is finding the minimum of current available day

Here is my solution

declare @date datetime, @day int

select @date='2009-12-22',@day=5

select
       
min(dateadd(day,14*(number),date))
from 
       
(
               
select dateadd(day,@day-datepart(weekday,@date),@date) as date
       
) as t,master..spt_values
where type='p' and dateadd(day,14*(number),date)>=getdate()

 

Posted by Madhivanan with no comments
Filed under: , ,

Replace data of one table with data of other table

One of the questions asked in a forum was about replacing data of one table by the data of another table
The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words

Here is my alternate solution

declare @Datatable table(Field1 nvarchar(50))

insert into @Datatable (Field1)
select 'testing for this' union all
select
'testing for this to'

 

declare @MatchTable table(LookFor nvarchar(50),Replacewith nvarchar(50))
insert into @MatchTable (LookFor,Replacewith)
select 'for','with' union all
select
'to','no'

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

select @replace='',@with ='',@sql=''

select
        @replace
=@replace +'replace(',
        @with =@with +''''+Lookfor +''','''+Replacewith+'''),'
from 
        @MatchTable

select @replace=@replace +'''~!@#'',',@with =SUBSTRING(@with,1,len(@with)-1)

select @sql=@sql+REPLACE('select '+@replace+@with,'~!@#',Field1)+' union all ' from @Datatable

select @sql=SUBSTRING(@sql,1,len(@sql)-10)

select * from @Datatable

exec(@sql)

 

Posted by Madhivanan with no comments
Filed under: , ,

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

Parsing a string

Now-a-days lot of questions are asked in the forum about extracting particular text from a string

Here I give some examples on how to do it

--Example 1 (Extracting tags and other informations)

declare @s varchar(100)

declare @temp table(id int identity(1,1),data varchar(100))

set @s='567<a>data<fg>ua<li>test<iu>welcome'

select @s=replace(replace(@s,'<','~!@#'),'>','~!@#')

while charindex('~!@#',@s)>0  
begin
        insert
into @temp(data)
        select substring(@s,1,charindex('~!@#',@s)-1)
        set @s=substring(@s,charindex('~!@#',@s)+4,len(@s))
end

insert into @temp(data)
select @s

--Extract the tags
select data from @temp where id%2=0

--Extract the data available outside the tags
select data from @temp where id%2=1

--Example 2 (Extract URLs)

declare @s varchar(1000)

declare @temp table(id int identity(1,1),data varchar(1000))

set @s='"ramkumar<a href="www.yahoo.com" mce_href="www.yahoo.com">hhh</a>mahesh<a title href="www.gmail.com" mce_href="www.gmail.com" />hhjjkjhsdf< as href="test.test.com" mce_href="test.test.com"'

while charindex('href=',@s)>0
begin
        insert
into @temp(data)
        select substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)
        set @s=substring(@s,charindex(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-                2),@s)+len(substring(@s,charindex('href=',@s)+6,charindex('.com',@s)-charindex('href=',@s)-2)),len(@s))
end select data from @temp

--Example 3 (Extract phone numbers with the format ddd-ddd-dddd where d denotes a number)

select 
        data
,
        substring(data,patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data),12) as ph_no
from
(
        select 'kashdk hasfjh sjkfhk h123-456-8765 kjsdkfjj h093 kljsdf903354512' as data union all
        select 'khasd ksfjh 345-245-9871kljk 90234 99jkxc 902304809n kljsdkfj9034'
) as t
where 
       
patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data)>0

--Example 4 (Extract amount starts with $)

select fruit,substring(amount,1,patindex('%[0-9][ ]%',amount+' ')) from
(
select 
        Fruit
, 
        substring(fruit,charindex('$',fruit),len(fruit)) as amount
        from 
        (
                select '9 Lemons cost 67 $99.99 on sale' as fruit union all
                select '$5.99 Apples 877 are on sale' union all
                select 'Where are the $65.99 lemons 7856' union all
                select 'Oranges costs $99.5'
        )as Pricing
) as t

Also refer these
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/08/03/regular-expression.aspx

Usage of Semicolons in SQL Server

As you know, semicolon is used as a statement terminator in almost all RDBMSs. However the usage of semicolon is optional in SQL Server except a few cases. Here are atleast the three cases where usage of semicolon is a must

1 At the start of the Common Table Expression (If any statement preceeds it)

2 If the procedure name is GO ,to execute it use at the end of GO (ex EXEC GO; as shown in Fun with GO)

3 At the end of Merge statement

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

SQL Server 2008 features that work only in Enterprise Edition

One of the new features available in SQL Server 2008 is compressed backup which takes backup of a database with mimimum possible size. The syntax is

backup database db_name to disk='backup_path'
with init,
compression

But this works only in Enterprise Edition of the SQL Server 2008 and not in other editions

If you want to know the new features that only work in Enterprise Edition,refer this
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Script out Procedures to seperate files

In this post script-out-procedures-and-functions-part-2, I showed how to script out the procedures in a single file

Here is another way to do the same but different file for each procedure. The file name will be the name of the procedure.

declare @sps table(proc_name varchar(100),texts varchar(8000))

insert into @sps
select name,'sp_helptext '''+name+'''' from sysobjects where xtype in ('p','fn')

create table scripts(sps varchar(8000))

declare @texts varchar(1000)
declare @file_name varchar(100)
declare @sql varchar(1000)

select
@texts=min(texts)from @sps

while @texts>''
Begin
        select
@file_name=proc_name from @sps where texts=@texts
        EXEC('insert into scripts(sps) EXEC '+ @texts) insert into scripts(sps) select 'GO'
        select @texts=min(texts)from @sps where texts>@texts 
        set @sql='bcp "select * from yourdb..scripts" queryout "c:\'+@file_name+'.txt" -c' 
        EXEC master..xp_cmdshell @sql
        truncate table scripts
End

drop table scripts

 

Posted by Madhivanan with 3 comment(s)
More Posts Next page »