Search

Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Monday, June 12, 2023

Top 40 Microsoft SQL database functions


1. GETDATE():

   - Explanation: Returns the current system date and time.

   - Example: SELECT GETDATE(); 

   - Output: 2023-05-31 10:15:30.123


2. DATEPART():

   - Explanation: Extracts a specific part (e.g., year, month, day) from a date.

   - Example: SELECT DATEPART(YEAR, GETDATE());

   - Output: 2023


3. DATEADD():

   - Explanation: Adds or subtracts a specific time interval from a date.

   - Example: SELECT DATEADD(DAY, 7, GETDATE());

   - Output: 2023-06-07 10:15:30.123


4. DATEDIFF():

   - Explanation: Calculates the difference between two dates in a specified time interval.

   - Example: SELECT DATEDIFF(DAY, '2023-01-01', '2023-01-15');

   - Output: 14


5. GETUTCDATE():

   - Explanation: Returns the current UTC date and time.

   - Example: SELECT GETUTCDATE();

   - Output: 2023-05-31 14:15:30.123


6. MONTH():

   - Explanation: Extracts the month from a date.

   - Example: SELECT MONTH(GETDATE());

   - Output: 5


7. YEAR():

   - Explanation: Extracts the year from a date.

   - Example: SELECT YEAR(GETDATE());

   - Output: 2023


8. DAY():

   - Explanation: Extracts the day of the month from a date.

   - Example: SELECT DAY(GETDATE());

   - Output: 31


9. DATENAME():

   - Explanation: Returns a string representing a specific part of a date.

   - Example: SELECT DATENAME(MONTH, GETDATE());

   - Output: May


10. LOWER():

    - Explanation: Converts a string to lowercase.

    - Example: SELECT LOWER('Hello World');

    - Output: hello world


11. UPPER():

    - Explanation: Converts a string to uppercase.

    - Example: SELECT UPPER('Hello World');

    - Output: HELLO WORLD


12. LEN():

    - Explanation: Returns the length of a string.

    - Example: SELECT LEN('Hello World');

    - Output: 11


13. REPLACE():

    - Explanation: Replaces all occurrences of a specified string with another string.

    - Example: SELECT REPLACE('Hello World', 'World', 'Universe');

    - Output: Hello Universe


14. SUBSTRING():

    - Explanation: Returns a substring from a specified string, starting at a specified position for a specified length.

    - Example: SELECT SUBSTRING('Hello World', 7, 5);

    - Output: World


15. LEFT():

    - Explanation: Returns the left part of a string with a specified length.

    - Example: SELECT LEFT('Hello World', 5);

    - Output: Hello


16. RIGHT():

    - Explanation: Returns the right part of a string with a specified length.

    - Example: SELECT RIGHT('Hello World', 5);

    - Output: World


17. CONCAT():

    - Explanation: Concatenates two or more strings.

    - Example: SELECT CONCAT('Hello', ' ', 'World');

    - Output: Hello World


18. LTRIM():

    - Explanation: Removes leading spaces from a string.

    - Example: SELECT LTRIM('   Hello World');

    - Output: Hello World


19. RTRIM():

    - Explanation: Removes trailing spaces from a string.

    - Example: SELECT RTRIM('Hello World   ');

    - Output: Hello World


20. FORMAT():

    - Explanation: Formats a value with the specified format and optional culture.

    - Example: SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');

    - Output: 31/05/2023


21. ISNULL():

    - Explanation: Returns the specified value if the expression is NULL, otherwise, returns the expression.

    - Example: SELECT ISNULL(NULL, 'N/A');

    - Output: N/A


22. NULLIF():

    - Explanation: Returns NULL if the two specified expressions are equal, otherwise, returns the first expression.

    - Example: SELECT NULLIF(10, 10);

    - Output: NULL


23. COALESCE():

    - Explanation: Returns the first non-null expression in the list.

    - Example: SELECT COALESCE(NULL, 'Value 1', 'Value 2');

    - Output: Value 1


24. RAND():

    - Explanation: Returns a random float value between 0 and 1.

    - Example: SELECT RAND();

    - Output: 0.759612873284


25. NEWID():

    - Explanation: Returns a uniqueidentifier (GUID) value.

    - Example: SELECT NEWID();

    - Output: 47E90FD0-7A23-4C1B-A9C8-9447F9532A29


26. ABS():

   - Explanation: Returns the absolute value of a numeric expression.

   - Example: SELECT ABS(-10);

   - Output: 10


27. CEILING():

   - Explanation: Returns the smallest integer greater than or equal to a numeric expression.

   - Example: SELECT CEILING(3.2);

   - Output: 4


28. FLOOR():

   - Explanation: Returns the largest integer less than or equal to a numeric expression.

   - Example: SELECT FLOOR(3.9);

   - Output: 3


29. ROUND():

   - Explanation: Returns a numeric expression rounded to the specified length or precision.

   - Example: SELECT ROUND(3.14159, 2);

   - Output: 3.14


30. SQRT():

   - Explanation: Returns the square root of a numeric expression.

   - Example: SELECT SQRT(16);

   - Output: 4


31. POWER():

   - Explanation: Returns the result of raising a numeric expression to a specified power.

   - Example: SELECT POWER(2, 3);

   - Output: 8


32. SIN():

   - Explanation: Returns the sine of the specified angle.

   - Example: SELECT SIN(45);

   - Output: 0.707106781186547


33. COS():

   - Explanation: Returns the cosine of the specified angle.

   - Example: SELECT COS(60);

   - Output: 0.5


34. TAN():

   - Explanation: Returns the tangent of the specified angle.

   - Example: SELECT TAN(30);

   - Output: -6.40533119664628


35. LOG():

   - Explanation: Returns the natural logarithm of a specified number.

   - Example: SELECT LOG(10);

   - Output: 2.30258509299405


36. EXP():

   - Explanation: Returns the value of Euler's number raised to the power of a specified exponent.

   - Example: SELECT EXP(2);

   - Output: 7.38905609893065


37. CHARINDEX():

   - Explanation: Returns the starting position of a substring within a string.

   - Example: SELECT CHARINDEX('World', 'Hello World');

   - Output: 7


38. ASCII():

   - Explanation: Returns the ASCII value of the first character in a string expression.

   - Example: SELECT ASCII('A');

   - Output: 65


39. PATINDEX():

   - Explanation: Returns the starting position of a pattern within a string.

   - Example: SELECT PATINDEX('%World%', 'Hello World');

   - Output: 7


40. SOUNDEX():

   - Explanation: Returns a four-character code to evaluate the similarity of two strings.

   - Example: SELECT SOUNDEX('Hello');

   - Output: H400


No comments:

Post a Comment