Madhivanan

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

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()

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))

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)

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))

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

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))

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'

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 '!'

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)

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

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

--Example 2 (Extract URLs)

declare @s 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"'

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

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

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

drop table scripts

Finding out problematic data in Bulk Insert data truncation error

Suppose you want to import data from a text file to a table, one of the options is to use Bulk Insert command
It allows to import data to the table, though one/more columns don't have enough size to get the maximum length of data from text file.It throws errors about data truncation with line numbers.

What if you want to find out the rows from the text file that are not imported

 Here is a way

Consider the following example

--Create a testing table

create table bulk_test
(
test_no     int,
test_name varchar(10)
)

GO

--The text file has the following data

id,    name
23,    test
8,      kjhasd
87,    sdf
23,    testing column
99,    ujh
45,    this is for testing only

--Bulk import query

Removing tags from a string

In http://ask.sqlteam.com, a question was asked about removing the comments from the HTML template

The comments have the following pattern

{/*}comment{*/}

The comments can be nested too

The task is to remove all the comments

Here is my solution

declare @string varchar(8000) ,@data varchar(8000)

select @string = 'AaaA{/*}XxxX{/*}YyyyY{*/}ZzzzZ{*/}BbbB{/*}CCC{*/}DdD'

select @string=replace(replace(@string, '{/*}','~{/*}'),'{*/}','~{*/}')

insert into @t(data)
select 
        substring(@string, n, charindex('~', @string + '~', n ) - n)
from
( 
        select number as n from master..spt_values where type='p'
) numbers
where 
        substring( '~' + @string, n, 1 ) = '~'

--Method 1

select @string

--Method 2

select @string =''

select
        @string =@string +substring(t2.data,5,len(t2.data))
from 
        @t as t1 inner join @t as t2 on t1.id+1=t2.id
where 
        t2.data like '{*/}%' and t2.id%2=1 and t2.data<>'{*/}'

select @string

Maximum length of each column

How do I find out the maximum length of each column in a table?

Here is the answer

declare @sql varchar(8000), @table sysname

select @table='your_table', @sql=''

select
        @sql=@sql+'select '''+@table+''' as table_name,'''+column_name+''' as column_name, 
                                max(len('+column_name+')) as column_length from '+@table+' union all '
from 
        information_schema.columns where table_name=''+@table+''

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

exec(@sql)

Dropping all temp tables

 In SQL Forum one of the posters asked a question about dropping all the temporary tables
There are many ways to do this
One of the ways to to run the following code

declare @sql varchar(8000)
set @sql=''
select @sql = @sql+' drop table '+name from tempdb..sysobjects where xtype='u'
exec(@sql)

Cursor statuses

Here are the queries that findout the cursors based on their statuses

select
        cur.cursor_name
from 
        sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle

Generate Fibonacci series - No loop, no recursion

Generating the Fibonnaci series generally involves in writing the code which has while loop or a recursive function

Peso posted the code using the Common table expression

Here are my methods that involve no loop or no recursion

1 Use Quirky update technique

declare @fib table(fib_id bigint,fib_val bigint)

declare @current_val bigint, @previous_val bigint,@new_val bigint

insert into @fib(fib_id,fib_val )

select top 30 row_number() over(order by name),1 from sysobjects

update @fib
set
        fib_val =@current_val,
        @current_val =@new_val +@previous_val,
        @previous_val=@new_val, 
        @new_val=@current_val

select * from @fib

2 Use Generalised formula

Method 1

select 
        floor(power((1+SQRT(5))/2.0,number )/sqrt(5)+0.5) as fib_number
from 
        master..spt_values
where 
        TYPE='p' and number between 0 and 100