Thursday, December 31, 2015

SQL - EXP() Mathematical Function

SQL - EXP()
Exponential is a mathematical function which is almost always understood to mean the natural exponential function. The function EXP() returns the exponential value for the numeric expression. I have come across the EXP built in function in SQL Server and this function plays a very important role in data analytics calculations.
This function has one mandatory argument and it returns float type exponential value. In SQL, we have use the following syntax:
SQL Syntax: EXP(Argument)
Argument: Any numeric expression.
Return Type: float.
Note: If value is numeric and not equal to zero then it will return float type otherwise it will return 1 for the 0 value. if argument is null then return type should be null.
The exponential function is equal to ex. The value for the constant float number e is approximately 2.718281828 (natural logarithm).
----- declare local veriables
declare @inpfloat float=1.2,
@inpInt INT=1,
@inpDecimal Decimal=1.2
 
----- output parameters
select EXP(@inpfloat) as 'Float EXP()',
EXP(@inpINT) as 'Int EXP()',
EXP(@inpDecimal) as 'Decimal EXP()'
 
Float EXP()
Int EXP()
Decimal EXP()
3.320116923
2.718281828
2.718281828
---- set value 0 for veriable
SET @inpfloat=0
SET @inpInt=0
SET @inpDecimal=0
 
----- output parameters
select EXP(@inpfloat) as 'Float EXP()',
EXP(@inpINT) as 'Int EXP()',
EXP(@inpDecimal) as 'Decimal EXP()'
Float EXP()
Int EXP()
Decimal EXP()
1
1
1
We can view the difference on the above results. this is because 0 is to the power anything is equal to 1.
Conclusion
In simple words EXP() function is used to get the value of e ^ x because Exp expects a float and returns one, so the results from a decimal type include an implicit conversion to and from float and in turn this behaviour is due to inherent imprecision of FP numbers.

Friday, December 25, 2015

SQL - LOG() Mathematical Function


SQL LOG()
This function was introduced within the release of SQL Server 2005. This function is used to calculate the natural logarithm of a number in SQL. In a very simple manner, we can say that the LOG function computes the logarithm of an expression. It plays a very important role in the data analytics.

Syntax
We can use this function by using below syntax-
LOG (Float Expression [, Base ] )
Base
Optional integer argument that sets the base for the logarithm. The base by which to compute the logarithm. When you do not specify a value, the function computes the natural logarithm of the expression by using e for the base where e is equal to 2.718281828459.
Float Expression
A numeric expression which is greater than zero. When the value is equal to or less than zero, LOG returns an NA value.
Return Types: The important thing is that it always return float type output.
Remarks: By default, LOG() returns the natural logarithm.  User can change the base of the logarithm to another value using the optional base parameter.
The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself:
EXP( LOG( n ) ) = n.
Calculating the logarithm
You can easily calculate the log for the specified float expression as given below:


--- declare float variable and set value
Declare @inpFloat float=10.24
--- declare int variable and set value
Declare @inpInt int=10
--- declare decimal variable and set value
Declare @inpDecimal int=10.24

---- select value from the log function
SELECT LOG(@inpFloat) as 'LOG Value from Float',
LOG(@inpInt) as 'LOG Value from Int',
LOG(@inpDecimal) as 'LOG Value from Decimal'

LOG Value from Float
LOG Value from Int
LOG Value from Decimal
2.32630162
2.302585093
2.302585093

--- declare base
Declare @base int=2

---- select value from the log function
SELECT LOG(@inpFloat, @base ) as 'LOG Value from Float',
LOG(@inpInt, @base) as 'LOG Value from Int',
LOG(@inpDecimal, @base) as 'LOG Value from Decimal'
LOG Value from Float
LOG Value from Int
LOG Value from Decimal
3.35614381
3.321928095
3.321928095


Now, we can see that log function returns two results. One is without base value and another one is with base value.

Conclusion
As we already know that this function plays important role in the data analytics calculations. Apart from this; logarithm is used by navigators, scientists, engineers, and others to perform computations more easily. In public-key cryptography application, discrete logarithm is used to generate keys.

Monday, December 21, 2015

SQL – Stuff Function

This is the most amazing function of T-SQL which is mostly used to delete a specified length of characters within a given string and replace it with another set of characters. Unlike the CONCAT function, which was introduced in SQL Server 2012, the STUFF function has been in prior versions of SQL Server too and hence we can use it in them too.
Syntax:-
STUFF (Character Expression, Start, Length, Replace With Expression)
Arguments: This function uses the following parameters.
Character Expression: Is an expression of character data. Character Expression can be a constant, variable, or column of either character or binary data.
Start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first Character Expression, a null string is returned. Start can be of type bigint.
Length: Is an integer that specifies the number of characters to delete. If length is longer than the first Character Expression, deletion occurs up to the last character in the last Character Expression. Length can be of type bigint.
Replace With Expression: Is an expression of character data. Replace With Expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of Character Expression beginning at start.

Important points to remember: There are some basic points always keep in mind as given below- 
  1. If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. 
  2. If the start position is 0, a null value is returned. 
  3. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
Important Facts - Unfortunately the stuff function only works on “strings” (char, nchar, varchar, nvarchar). If you need to use it on a numeric data type you will have to convert it to a string and back again.
Example 1: Generate a Comma-Separated List
Stuff function is very useful if we want to add comma-separated list. If we want to capture all comments against any particular topic then stuff function comes into the picture such as given below-
--- declare table variable to store the comments
DECLARE @UserInputs TABLE
(  
    PollId Int, PollSubject Varchar(250), UserComments Varchar(250)
)
----- Insert Values into table variable
INSERT INTO @UserInputs (  PollId, PollSubject, UserComments)
VALUES
(1, 'Most favourite  super hero?', 'Superman' ),
(1, 'Most favourite  super hero?' ,'Batman' ),
(1, 'Most favourite  super hero?' ,'Ironman'),
(1, 'Most favourite  super hero?' ,'Wolverine'),
(2, 'Most favourite  movie?', 'Titanic' ),
(2, 'Most favourite  movie?' ,'The Note Book' ),
(3, 'Most favourite  Game?' ,'Cricket'),
(3, 'Most favourite  Game?' ,'Football')
 ----- Table Variable output
SELECT PollId, PollSubject, UserComments FROM @UserInputs
PollId
PollSubject
UserComments
1
Most favourite  super hero?
Superman
1
Most favourite  super hero?
Batman
1
Most favourite  super hero?
Ironman
1
Most favourite  super hero?
Wolverine
2
Most favourite  movie?
Titanic
2
Most favourite  movie?
The Note Book
3
Most favourite  Game?
Cricket
3
Most favourite  Game?
Football
 ----- Comments by using stuff function
SELECT DISTINCT PollId, PollSubject,
UserInput=STUFF((SELECT ',' + UserComments
                                                        FROM @UserInputs
                                                        Where PollId=UI.PollId
                                                        ORDER BY PollSubject
                                                        FOR XML PATH('')), 1, 1, '')
from @UserInputs UI
ORDER BY UI.PollId
PollId
PollSubject
Output
1
Most favourite super hero?
Superman,Batman,Ironman,Wolverine
2
Most favourite  movie?
Titanic,The Note Book
3
Most favourite  Game?
Cricket,Football


We are aware that all that STUFF is doing is trimming the leading , off of the text that FOR XML PATH is generating.
Example 2: Insert One String Into Another String at a Specific Location
We can use the stuff function to replace or insert new string into the existing string as given below:
---- declare table variable to store the comments
DECLARE @UserInputs TABLE
(  
    PollId Int, PollSubject Varchar(250), UserComments Varchar(250)
)
----- Insert Values into table variable
INSERT INTO @UserInputs (  PollId, PollSubject, UserComments)
VALUES
(1, 'Most favourite  super hero is ? for kids', 'Superman' ),
(2, 'Most favourite  movie is ? in Cinema', 'Titanic'),
(3, 'Most favourite  Game is ? in the world.' ,'Cricket')
----- Table Variable output
SELECT PollId, PollSubject, UserComments FROM @UserInputs
PollId
PollSubject
UserComments
1
Most favourite  super hero is ? for kids
Superman
2
Most favourite  movie is ? in Cinema
Titanic
3
Most favourite  Game is ? in the world.
Cricket
----- Insert One String Into Another String at a Specific Location
----- by using stuff function
SELECT PollId
,UserInputs=STUFF(PollSubject, CHARINDEX('?', PollSubject), 1, UserComments)
 FROM @UserInputs
 ORDER BY PollId
PollId
UserInputs
1
Most favourite  super hero is Superman for kids
2
Most favourite  movie is Titanic in Cinema
3
Most favourite  Game is Cricket in the world.
  

SQL Stuff () Vs REPLACE()
One of the more important aspects of STUFF and at least one huge difference between STUFF and REPLACE when both are used to replace things.
For example, STUFF can stuff a string into an existing string without replacing any of the original string. REPLACE cannot.

REPLACE will replace ALL instances of the search string with the replacement string in the original string. STUFF only does one instance and must be tightly controlled by position rather than by existence.
Stuff () - This function can be used for delete a certain length of the string and insert a new string in the deleted place.
STUFF Syntax: STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)
String - String to be overwritten
StartPos - Starting Position for overwriting
LengthofReplaceChar - Length of replacement string
ReplaceString - String to overwrite

REPLACE()- This function replaces all the occurrences of a string expression with a new string within an input string. 
REPLACE Syntax: REPLACE (String, StringToReplace, StringTobeReplaced)
String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite
Conclusion
STUFF is likely to be more flexible. Just depends on your needs. The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Popular Posts