View Column Names in DB2

January 19, 2012

select colname

from syscat.columns

where tabschema = ‘MY_SCHEMA_NAME’

and tabname = ‘MY_TABLE_NAME’

Add Days to TimeStamp Field in DB2

May 6, 2011

To add days to a timestamp field in DB2 is surprisingly simple. For example, to add 100 days:

Select myFieldName + 100 DAYS from myTable

Determine a person’s age:

Select YEAR ( CURRENT DATE - MyDateField) from myTable

Map Integer to Alpha Character

April 13, 2011

In SQL Server:

SELECT  CHAR(SomeValue % 26 + 65)

In DB2:

SELECT  CHR(MOD(SomeValue ,26)+65) FROM sysibm.sysdummy1

Format number as string with thousand separator in SQL Server

January 11, 2011

Here is the  expression:

select … replace(convert(varchar,convert(Money, myField),1),’.00′,'’)  from ….

Works fine thanks to this thread.

SQL Server Select From Select

November 25, 2010

If you need to select individual columns from your select statements, you need to add an alias to your first select statement. Example:

select colname from
(select name, colname from fieldsTable
where name like ‘Changed By%’
or name like ‘Assigned To%’)
sel

Make Fixed Fonts Resizable in IE

June 9, 2010

Visit http://simonwillison.net/2002/Sep/1/fontSizeBookmarklet/ to get a bookmarklet to make fixed fonts resizable in IE.

Here is the script just in case:

javascript:function%20r(o)%20{%20if(o.style)%20{o.style.fontSize%20=%20’small’;%20o.style.lineHeight=’1.2em’;}

var%20cn=o.childNodes,i;%20for%20(i=0;i<cn.length;++i)%20r(cn[i]);%20}%20r(document.body);

 

 

 

 

Rowid Equivalent in SQL Server

November 27, 2009

There is no direct equivalent for the Oracle rowid in SQL Server but there are ways to achieve the same functionality:

Refer to : http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

The first one (and probably the easiest for simple select statements) is as follows:

Select rank() OVER (ORDER BY myColumn) as rank, column1, …. from …

Oracle Equivalent for “Use Database” Command

September 23, 2009

ALTER SESSION SET CURRENT_SCHEMA = schema

Substring in Sql Server

July 9, 2009

Suppose values in your fields contain a period, and you want to return all characters that come before it. This is how you do in SQL Server:

select substring(column1, 1, charindex(’.', column1)-1 )
from myTable

Capitalize First Letter in JavaScript

June 10, 2009

Here is a function based on excellent code from Philip M at http://www.codingforums.com/showthread.php?t=168661 that capitalizes the first letter of each word:

function caps(str){
    return str.toLowerCase().replace(/\b[a-z]/g,function(w){return w.toUpperCase()});
}

 

Check if Column is Empy in SQL Server

April 15, 2009

WHERE datalength(ColumnName)> 0  shows non-empty fields

Replace single quotes in transact-sql

March 9, 2009

If you have single quotes in your text values, and you need to insert them in a SQL Server database the recommended approach is to use parameterized stored procedures. They will handle single quotes seamlessly.

However, there may be cases when you may need to construct an insert statement without resorting to parameters. In such cases, to handle text that contains single quotes you may have to use the Transact-SQL Replace function:

REPLACE ( string_expression , string_pattern , string_replacement )

An example that escapes single quotes by adding another single quote before it:

Declare @temp as varchar(64)
Set  @temp= (select …  from …)
Set @temp = Replace(@temp, ‘”’,””'’)

In other words, you need 4 single quotes as a second argument, and 6 single quotes as a second argument to escape single quotes.

Export Unicode From GridView to Excel

February 12, 2009

This solution was found on http://razvan.cosma.name/weblog/index.php?entry=entry090116-161656

private void ExportToExcel(string strFileName, GridView gv)
        {
            Response.ClearContent();
            Response.ContentType = "application/excel";
            Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
            Response.Charset = "";         
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
            this.EnableViewState = false;
            System.IO.StringWriter sw = new System.IO.StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Write(sw.ToString());    
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

 

 

 

Some Regular Expressions for Java

February 11, 2009

If you need to find text lines that start with an alphanumeric characters:

String reg ="\\w+";  

In other words, an alphanumeric character that gets repeated 1 or more times.

 To find text lines that start with a whitespace and contain other characters after it, use the following regex:

String  reg = "\\s.+";

 That is, first find a whitespace, then any character repeated 1 or more times.

 

Select Individual Columns From Table Information Schema

January 29, 2009

The ‘desc’ command to get a list of table fields that is common in Oracle and MySql will fail in SQL Server. It turns out it is not sql-standard compliant. Instead, SQL Server uses the following command:

sp_columns myTableName

This will display detailed information about table structure.

If you need just individual columns, use the following command:

select column_name, data_type, character_maximum_length, *  
from information_schema.columns
where table_name = ‘myTableName’

order by ordinal_position