Wednesday, June 23, 2010

The future of ebook readers

I once thought that devices dedicated to reading books were a great idea.  But with the intersection of wifi and a screen, it only makes sense for the device to be able to render the rich content of the internet as well.  The era of dedicated ebook readers is drawing to a close. 

I believe the iPad will have tough competition from other vendors who will now realize the value of a device that has enough real estate to view content as it should be viewed.

Watch out for Android Tablets, Windows Tablets, and Asus Tablets.

Tuesday, June 22, 2010

Finding a column name which contain x

This will list all columns in the table tblName of interest which contain the letter x, along with the dataType and length:
select  tableName=tbl.name ,columnName=c.name, typeName=t.name, length= case when t.name='nvarchar' then c.length/2 else c.length end
from  sysobjects tbl 
left join syscolumns c 
 join systypes t on c.xtype=t.xtype and t.name != 'sysname'
 on c.id=tbl.id
where tbl.name like '%tblName%' 
and c.name like '%x%' 

Using this, you can find columns of interest in tables or views.

This was tested against SQL Server 2000.

Saturday, June 19, 2010

Brute force method of deleting all objects in database based on naming convention

I did this to clean out from my database all the tables which were created by DotNetNuke.

Run the query below to get the delete object script. Then run it repeatedly till nothing is left. Because there might be dependencies between objects, some objects might be left behind the first time you run this.

 
select dropCmd= 'if object_id(''' + name + ''') is not null  drop ' +
case when xtype= 'P' then 'procedure'
 when xtype = 'U' then 'table'
 when xtype = 'FN' or xtype = 'TF' then 'function'
 when xtype = 'V' then 'view'
 else ' ******unhandled object type ****** 'end + ' dbo.' +  name
 from sysobjects where name like 'dnn%_'

Monday, June 14, 2010

Monday, June 07, 2010

Enjoying the beach: Priceless


MySQL Management Tools

MySQL ODBC Driver: Allows you to set up ODBC connections.
MySQL Workbench:  It's a SqlServer Management Studio equivalent for MySQL.

Sunday, June 06, 2010

How to connect the MySql to Visual Studio

I was accustomed to working with SQL Server databases exclusively and it never occurred to me to try this.

Windows Workflow Foundation

Why and when to use it.

Friday, June 04, 2010

TSQL for extracting email address from text field

I was working on a project which involved data migration and I needed a way to extract email addresses out of the notes field.  So here is what I came up with.

Create FUNCTION svf_ExtractEmailAddress 
(
 @notes varchar(1000), @incident int /*1 to get the first occurance, 2 to get the second, etc*/
)
RETURNS varchar(100)
AS
BEGIN
 declare @result varchar(100), @iStart int, @iMid int, @iEnd int, @temp varchar(1000), @incidentCounter int
 
 --replace newline (10/13) and tab(9) with space
 set @notes = REPLACE(REPLACE(REPLACE(@notes,CHAR(13),' '), char(10), ' '), char(9), ' ')
 set @incidentCounter=1
 
 set @iMid = patindex('%[A-Z1-9]@[A-Z1-9]%', @notes) --the location of the character righth before the @ sign
 
 while (@incidentCounter < @incident and @iMid > 0)
 Begin  
  set @notes = substring(@notes, @iMid +1, 1000)
  set @iMid = patindex('%[A-Z1-9]@[A-Z1-9]%', @notes) --the location of the character righth before the @ sign
  set @incidentCounter = @incidentCounter+1
 End
 
 if (@iMid =0) return ''
 
 --locate the first space before the at symbol
 set @temp = substring(@notes,0, @iMid )-- temp contains everything BEFORE the @sign
 set @iStart = charindex(' ', @temp)
 set @temp = substring(@notes,@iStart, @iMid-@iStart )
 while (charindex(' ', @temp)>0) 
 Begin
  set @iStart = @iStart + charindex(' ', @temp)
  set @temp = substring(@notes,@iStart, @iMid-@iStart)
 End
 --locate the first space after the @ symbol
 set @temp = substring(@notes,@iMid, 1000 ) -- temp contains everything AFTER the @sign
 set @iEnd = patindex('%[ ,:;'+CHAR(13)+CHAR(10)+']%', @temp)
 set @iEnd = 
  case when @iEnd=0 then 1000 
  else @iMid + @iEnd -1 end
 /*
 set @result = '@iMid: ' + convert(varchar(9), @iMid) 
  + ' @iStart: ' + convert(varchar(9), @iStart) 
  + ' @iEnd: ' + convert(varchar(9), @iEnd) 
  + ' email: ' + substring (@notes, @istart, @iEnd - @istart)
  --*/
 set @result = substring (@notes, @istart, @iEnd - @istart)
 RETURN  @result 
END

Example usage:

declare @note varchar(1000) set @note= 'bird@gmail.com hello happy chipmunk@yahoo.com, you are nice guy so dog@gmail.com cat@gmail.com'
select e1=dbo.svf_ExtractEmailAddress(@note, 1), e2=dbo.svf_ExtractEmailAddress(@note, 2),
 e3=dbo.svf_ExtractEmailAddress(@note, 3), e4=dbo.svf_ExtractEmailAddress(@note, 4), 
 e5=dbo.svf_ExtractEmailAddress(@note, 5)

And here is the result: