SQL Functions Most Used
Posted by venkat on December 15, 2007
SQL Functions
* With(NOLOCK)::
Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables.
Eg:- Select CashDeposited from ClientcashMAster With(NOLOCK) where ClientCode
Using ROWLOCK politely asks SQL Server to only use row-level locks.
* charindex(@FindStr, @InString, @FromPosition)::
Returns the starting position of the specified expression in a character string.
* Exists() Specifies a subquery to test for the existence of rows.
* REPLICATE (jj,10):: Repeats a string value a specified number of times.
* SUBSTRING ( expression ,start , length )
Returns part of a character, binary, text, or image expression.
Length: Is a positive integer that specifies how many characters or bytes of the expression will be returned.
* ISNULL ( check_expression , replacement_value )::
Replaces NULL with the specified replacement value.
* RIGHT ( character_expression , integer_expression )
Returns the right part of a character string with the specified number of characters.
Eg: SELECT RIGHT(FirstName, 5) AS 'First Name' FROM Person
* REPLACE ( Search_strExp , Find_strExp , Repl_strExp )
* CAST (expression AS data_type [(length)])
CONVERT (data_type [(length)],expression [,style]) Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
CAST is more ANSI standard, Where as CONVERT is Microsoft specific and more powerful.
CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers. CONVERT gives you more control over the formatting
CAST is the more ANSI-standard of the two functions, meaning that while it’s more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it’s also less powerful.
CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it’s best to use CAST first
