Programmer Collection

[mssql] How to get length of Text, NText and Image columns in SQL Server

2011-09-19T21:52:00.000-07:00

Problem There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types? SolutionIn addition to the

[mssql] ^ (Cap) is not the same with SQUARE()

2011-08-19T00:19:00.000-07:00

Never use the ^ (cap sign) to replace the SQUARE() function. SELECT 1024 ^ 2result: 1026 SELECT SQUARE(1024)result: 1048576 You see the difference!

[EXCEL] Week Number

2011-07-05T02:02:00.000-07:00

Introduction Many applications and organizations use the week number of a date for some sort of identification, classification, or formatting purpose. However, what constitutes a week number, specifically when does Week 1 begin, is not always the same from one organization or application to another. This page describes worksheet formulas and VBA procedures that you can use to calculate a week

[vb] Terbilang (also for Visual Basic Editor in EXCEL)

2011-05-08T22:05:00.000-07:00

Syntax: Terbilang(12345) Result: Dua Belas Ribu Tiga Ratus Empat Puluh Lima Rupiah Option Explicit Function Terbilang(InputAngka As Double) As String Dim I%, strMaxDigit$, arrLevel, arrAngka, TempRp$ arrAngka = Array("", "Se", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Delapan", _"Sembilan", "Sepuluh") arrLevel = Array("Triliun ", "Miliar ", "Juta ", "Ribu ", "") strMaxDigit =

[mssql] How to handle a Delimited String similar to handle an Array() of Values

2010-12-07T17:21:00.000-08:00

Often there is the desire to pass data to T-SQL as an array() of values, yet T-SQL does not provide an array() datatype. The following presents one method to simulate handling an array() using T-SQL. The client application creates a single-variate array(), and then passes that array() as a VARCHAR() parameter to a SQL Stored Procedure. The Stored Procedure, in turn, passes that VARCHAR(), or

[mysql] Selecting Random Selection Of Rows From A Database Table

2010-11-25T00:17:00.000-08:00

The begining of this story is a PHPNuke module. The module has a block that picks a random item from a database table and displays it. The content of the table never changes. Here is how the programmer of the block does it: He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then

[mssql] Selecting Random Selection Of Rows From A Database Table

2010-11-24T23:31:00.000-08:00

I found a nice tip for selecting random rows from within a SQL Server 2000 database. Well actually, pseudorandom. Since my undergraduate thesis was on the topic of pseudorandom number generation, I might as well be precise. For some reason, my non-geek friends find it awfully funny when I mention pseudorandom numbers. I digress. In order to select 10 records from some table at random, try

[mssql] Compare the records of two tables in SQL Server

2010-11-02T18:51:00.000-07:00

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well. UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs

[mssql] Database in Recovery Status

2010-09-07T17:38:00.000-07:00

Q: I have a database currently showing to be in recovery. I have not been able to find a GUI method to monitor the progress (to determine when it might complete) so is there a command line T-SQL method to monitor the recovery process? A: If this is 2005, this might be what you need: