Import from Excel Sheet into Sql Table with filtering

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Data.xls',

'SELECT * FROM [Sheet1$] where [col_name] <> '' ')

SQL FUNCTION ExtractNumbers

SQL FUNCTION ExtractNumbers

Create Function ExtractNumbers

(

@str_passed varchar(max)

)

Returns varchar(50)

AS

BEGIN

    declare @val varchar(50), @i int

    select @val = '', @i = 1

    while (@i <= len(@ str_passed))

        select @val= @val+ (CASE when substring(@ str_passed,@i,1) like '[0-9]' 

                                  then substring(@ str_passed,@i,1) else '' END),

              SET @i = @i + 1

    Return @val

END

Usage:-

SELECT SrNo,  dbo. ExtractNumbers(AlphaNumCol) as NumColData from TableName

Drop commas from '45,22,135'

or Drop dash & braces from '(91)123-456-789'

or Drop alphabets from 'C99B33A1234'

RupeeMail

Invitation to join RupeeMail!

---

Hi ,

I have something interesting for you, RupeeMail!

It’s really amazing! You get paid to open & read the contents of RupeeMail. You receive promotional offers & special discounts in RupeeMail.

Interestingly RupeeMails will reach you based on the preference list you opted for.

Create your RupeeMail Account & refer your friends to earn launch referral bonus on every new registration.

Try this... http://www.rupeemail.in/rupeemail/invite.do?in=MzU4NTYzJSMlY2dlU2w1NldxdlhQelQ0TThSSFVJOTNtUQ==

RupeeMail, It pays

Sushil Soni

Click on below link to navigate to RupeeMail.com

http://www.rupeemail.in/rupeemail/invite.do?in=MzU4NTYzJSMlY2dlU2w1NldxdlhQelQ0TThSSFVJOTNtUQ==

Scalar User Defined Functions and Computed Columns

Scalar User Defined Functions and Computed Columns

Came across an interesting SQL problem where a customer has a table with a primary key and X numerical fields. For each record the maximum value in these X fields was needed.

The issue was how to accomplish this efficiently? Or more accurately the least inefficient way of doing it is.
One option suggested was to have to UNION the table with itself X times and then do a MAX aggregation since there was a lot of data.

The more interesting option, which it turns out not many people are aware of, is to use a Scalar User Defined Function (UDF) to calculate the maximum value.

Here's an example of this in action:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

Basically the scalar UDF is called for each row returned from a SELECT. The column itself is not actually stored in the SQL Server table. Any INSERT and UPDATE on the table do not calculate the value for the computed column. Now obviously this is an overhead that may not be acceptable if the table is very static and read a lot. Here, it may be more efficient to perform the calculation once on INSERT or UPDATE of the table with INSTEAD OF triggers or another similar method.

Scalar User Defined Functions and Computed Columns

Scalar User Defined Functions and Computed Columns

Came across an interesting SQL problem where a customer has a table with a primary key and X numerical fields. For each record the maximum value in these X fields was needed.

The issue was how to accomplish this efficiently? Or more accurately the least inefficient way of doing it is.
One option suggested was to have to UNION the table with itself X times and then do a MAX aggregation since there was a lot of data.

The more interesting option, which it turns out not many people are aware of, is to use a Scalar User Defined Function (UDF) to calculate the maximum value.

Here's an example of this in action:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

Basically the scalar UDF is called for each row returned from a SELECT. The column itself is not actually stored in the SQL Server table. Any INSERT and UPDATE on the table do not calculate the value for the computed column. Now obviously this is an overhead that may not be acceptable if the table is very static and read a lot. Here, it may be more efficient to perform the calculation once on INSERT or UPDATE of the table with INSTEAD OF triggers or another similar method.

Javascript - Compare two Dates

Javascript - Compare two Dates

function CompareDates() {
var txtEffDt = document.getElementById("txtEffectiveDt").value.split("/");
var txtExpDt = document.getElementById("txtExpiryDt").value.split("/");
var date1 = new Date(txtEffDt[2]+'/'+txtEffDt[0]+'/'+txtEffDt[1]);
var date2 = new Date(txtExpDt[2]+'/'+txtExpDt[0]+'/'+txtExpDt[1]);

if (date1 > date2) {
alert("Expiry Date cannot be less than Effective Date");
return false;
}
}

Javascript - Convert Number to words

Javascript - Convert Number to words

link the script into the head of your page using the following code:

The final step is to call the script to perform the conversion to words for you. To get a number converted to words you just need to call the function passing it the number you want to convert and the corresponding words will be returned.

var words = toWords(num);

// Convert numbers to words

// American Numbering System

var th = ['','thousand','million', 'billion','trillion'];

// uncomment this line for English Number System

// var th = ['','thousand','million', 'milliard','billion'];

var dg = ['zero','one','two','three','four', 'five','six','seven','eight','nine']; var tn = ['ten','eleven','twelve','thirteen', 'fourteen','fifteen','sixteen', 'seventeen','eighteen','nineteen']; var tw = ['twenty','thirty','forty','fifty', 'sixty','seventy','eighty','ninety']; function toWords(s){s = s.toString(); s = s.replace(/[\, ]/g,''); if (s != String(parseFloat(s))) return 'not a number'; var x = s.indexOf('.'); if (x == -1) x = s.length; if (x > 15) return 'too big'; var n = s.split(''); var str = ''; var sk = 0; for (var i=0; i < 3="="2)" sk="1;}" sk="1;}}" 3="="0)" sk="1;}" 3="="1)" sk="0;}}" y =" s.length;" i="x+1;">