- T-SQL SQL Server 2005 Data Warehouse Scale long time ago I had a problem with a T-SQL function and no one could help, such because once called RESERVED WORDS, WORDS or correct CLAVEZ FUNCIONES... pero no, la verdad era que no nos sabemos las FUNCIONES de T-SQL tan bien como pensamos. Mencionare algunas funciones con algunos ejemplos
En si las Funciones son elementos de sintaxis que toma cero, uno o  más valores de entrada que van a devolver un valor escalar o un conjunto de tabla de valores. En SQL Server 2005, la funciones se clasifican en funciones deterministas y no deterministas.
Funciones deterministas siempre devuelven los mismos resultados para un conjunto específico de valores de entrada.
Funciones no determinista devuelven resultados diferentes cuando a que se les llama repetidamente con el mismo conjunto de valores de entrada. Funciones de SQL Server también pueden classified by type of input that accepts the role.
STRING FUNCTIONS
SUBSTRING: retrieve a portion of the input string. USE
AdventureWorks2008
GO SELECT SUBSTRING (FirstName , 1 , 4 ) AS 'First Name' FROM
Person. WHERE
Person Person . BusinessEntityID = 1545
UPPER and LOWER: convert a sensitive data to uppercase and vice versa
AdventureWorks2008
USE GO
SELECT UPPER (FirstName ) as 'First Name' FROM
Person . WHERE
Person Person . BusinessEntityID = 883
STUFF: deletes a specified length of characters and inserts another set of characters in a specified location.
LTRIM: removes spaces initial chain.
RTRIM: remove trailing blanks from a string.
REPLACE: replace all occurrences of a specific string in a given string with another string.
SOUNDEX Returns a four-character (SOUNDEX) to assess the similarity of two strings.
DIFFERENCE: returns an integer value indicating the difference between the SOUNDEX values \u200b\u200bof two character expressions
DATE AND TIME FUNCTIONS
GETDATE: deterministic function that returns the date of current system and hour.
DATEDIFF: deterministic function which returns the number of blackout dates and time between two dates.
USE AdventureWorks2008
GO SELECT DATEDIFF ( MONTH , OrderDate , GETDATE ()) AS 'many months'
FROM Sales . WHERE SalesOrderHeader
SalesOrderID = 43659
DATEADD: deterministic function returns a date value and time again based on adding an interval to the specified date.
USE AdventureWorks2008
GO SELECT DATEADD ( YEAR , 7 , HireDate ) AS 'Date of completion of 3 years'
FROM HumanResources . Employee WHERE
BusinessEntityID = 30
DATEPART: deterministic function except when used as DATEPART (dw, date). depends DW, datepart day week, the value set by SET DATEFIRST, which sets the first day of the week. Returns an integer representing the specified datepart.
AdventureWorks2008
USE GO SELECT
DATEPART (DAY , BirthDate) AS 'Meets Years'
FROM HumanResources . Employee WHERE
BusinessEntityID = 120
DATENAME: deterministic function that returns a string representing the specified datepart. Nondeterministic functions that returns a string representing the specified datepart.
AdventureWorks2008
USE GO SELECT
DATENAME ( month, BirthDate ) As 'Month Meets Years'
FROM HumanResources . Employee WHERE
BusinessEntityID = 120
DAY, MONTH, and YEAR: deterministic function that returns an integer that represents part of the day, month and year respectively.
GETUTCDATE: function deterministic returns the datetime value representing the current UTC (Coordinated Universal Time or Greenwich Mean Time).
SELECT GETUTCDATE ()
FROM HumanResources . Employee WHERE
BusinessEntityID = 120
Mathematical Functions
ABS: Returns the absolute value of a positive number.
SELECT ABS (- 1.0 ), ABS (- 0.0355), ABS (1.12 )
COS and SIN: returns the cosine and sine of the angle, respectively.
SELECT COS (30.3 ) SELECT
ABS (SIN (30.3 ))
POWER: returns of a given expression to the specified power.
POWER SELECT ( 3 , 4 ) POWER ( 5 , 2)
ROUND: displays a numeric expression, rounded to the specified length.
SELECT ROUND (23.23456 , 4 )
FLOOR and CEILING: return the largest integer less than or equal to, and the smallest integer greater than or equal to given numerical expression, respectively.
SELECT CEILING (156,234 )
SELECT FLOOR (156,234 )
SELECT CEILING (- 34,564)
SELECT FLOOR (- 34,564)
and SQUARE SQRT: Returns the square and the square root of a certain number, respectively.
SELECT SQRT (64 )
SELECT SQUARE ( 9 )
CONVERSION FUNCTIONS
conversion functions are CAST and CONVERT. CAST and CONVERT functions explicitly convert an expression of one data type to another data type. The difference between CAST and CONVERT is that CAST is ANSI, while CONVERT is not ANSI. In addition, CONVERT style parameter is optional.
SELECT CAST ( '19700926 'AS SMALLDATETIME )
SELECT CONVERT ( SMALLDATETIME , '19700926 ' )
SELECT Convert ( VARCHAR (MAX ) GETDATE (), 3)
SELECT Convert ( VARCHAR (MAX ) GETDATE (), 103 )
SYSTEM function
host_name: deterministic function that returns the name of the workstation and is good for conducting audits.
SELECT HOST_NAME ()
XACT_STATE: deterministic function that informs the transaction state of a session, indicating whether the session has an active transaction, and if the transaction is capable of being committed.
SELECT XACT_STATE ()
SYSTEM_USER: deterministic function that allows a system-provided value for the current logon to inserted into a table when not specified defaults.
SELECT SYSTEM_USER
CURRENT_TIMESTAMP: deterministic function that returns the current date and time.
SELECT CURRENT_TIMESTAMP
DATALENGTH: deterministic function that returns the number of bytes used to represent any expression.
select DATALENGTH ( 'EXPRESSION Length' )
SUSER_SNAME: deterministic function that returns the name of the login ID of the user ID security.
SELECT SUSER_NAME (1 ) sa
SELECT SUSER_NAME (2 ) public
SELECT SUSER_NAME (3 ) sysadmin
SELECT SUSER_NAME (4 ) securityadmin
SELECT SUSER_NAME (5 ) serveradmin
SUSER_NAME SELECT (6 ) setupadmin
METADATA FUNCTIONS
DB_NAME: displays the name of current database.
OBJECT_ID: Displays the object ID the current database. OBJECT_NAME
displays the name of the database object.
USE AdventureWorks2008 ;
GO DECLARE @ Object int;
SET @ Subject = (SELECT OBJECT_ID ( 'HumanResources.Employee ' , U ));
SELECT name , object_id, type_desc
FROM sys.
objects WHERE name = OBJECT_NAME (@ object )
NULL FUNCTIONS
ISNULL: NULL replacement with the specified value of replacement. NULLIF
: returns NULL if the two had specified expressions are equivalent.
COALESCE: returns the first nonnull expression among its arguments.
USE AdventureWorks2008 ;
SELECT AVG ( ISNULL (Weight , 50 )) FROM
Production. Product ;
