Math and Numeric function in SQL Server

These all are the math function in SQL which helps users to manipulate on the number.

Math and Numeric function in SQL Server

SQL server absolute value function:-

ABS() function in SQL is used to get the absolute value of a number.

 Syntax : -

ABS(number)

  ACOS() SQL function:-

ACOS() function gives the arc cosine of a number.

 Syntax : -

ACOS(number)

ASIN() SQL function:-

ASIN() in SQL gives the arc sine of a number.

 Syntax : -

ASIN(number)

ATAN() SQL function:-

ATAN() method in SQL gives the arctangent of a number.

 Syntax : -

ATAN(number)

ATAN() SQL function:-

ATAN2() gives an arc tangent of two numbers in SQL.

 Syntax : -

ATN2(a, b)

AVG() SQL:-

AVG() function in SQL tell you about the average value of an expression.

Syntax : -

AVG(expression)

SQL CEILING() method:-

CEILING() method in SQL helps you to give the smallest integer value that is larger than or equal to a number. 

Syntax : -

CEILING(number)

SQL COUNT():-

COUNT() in SQL tells you about the number of records returned by a select query in Database. 

Syntax : -

COUNT(expression)

SQL COT():-

COT() function in SQL returns the cotangent of a number. 

Syntax : -

COT(number)

SQL DEGREES() function:-

SQL DEGREES() use to convert the radians value to degrees.

Syntax : -

DEGREES(number)

EXP() function:-

EXP() method in SQL raise the power of e to the specified number.

Syntax : -

EXP(number)

FLOOR() in SQL:-

FLOOR() function in SQL gives the largest integer value that is smaller than or equal to a number.


Syntax : -

FLOOR(number)

LOG() SQL:-

LOG() in SQL gives the logarithm value of any specified number or the logarithm of any number to the specified base.


Syntax : -

LOG(number, base) -- Syntax for SQL Server

LOG10() SQL:-

LOG10() gives the logarithm value with the base 10 of any specified value


Syntax : -

LOG10(number)

MAX() in SQL:-

MAX() function in SQL find the maximum number from a set of values.

Syntax : -

MAX(expression)

MIN() in SQL:-

MIN() function in SQL find the minimum number from a set of values.

Syntax : -

MIN(expression)

PI() in SQL:-

As its name says PI() function in SQL gives the value of PI.


Syntax : -

PI()

POWER() in SQL:-

POWER() in SQL server function gives the value which is raised to the power of any specified number.


Syntax : -

POWER(a, b)

RADIANS() in SQL:-

RADIANS() function used to convert the degree value into radians.

Syntax : -

RADIANS(number)

RAND() in SQL:-

RAND() function in SQL gives the random number between 0 (inclusive) and 1 (exclusive) if seed is specified then it would return the repeatable sequence of random numbers.


Syntax : -

RAND(seed)

ROUND() in SQL:-

ROUND() function in SQL gives the round number to a specified number of decimal places.

Syntax : -

ROUND(number, decimals, operation)

SQRT() method in SQL:-

SQRT() function in SQL gives the square root of a number.

Syntax : -

SQRT(number)

SQUARE() method in SQL:-

SQUARE() SQL function gives the square of a number. 

Syntax : -

SQUARE(number)

SUM() function in SQL:-

SUM() SQL function calculates the sum of a set of values.

Syntax : -

SUM(expression)

SUM() function in SQL:-

SUM() SQL function calculates the sum of a set of values.

Syntax : -

SUM(expression)

Read More

SQL server functions and build-in functions

These all are the function SQL Server which helps the user manipulate the SQL QUERY. You can learn string, numeric, date, conversion, and some advanced functions in SQL Server.

SQL server functions and built-in functions

ASCII() Function : - 

ASCII() function in SQL gives the value for the specified character.

Syntax : - 

ASCII(character)

CHAR() Function : - 

CHAR() function in SQL reads the character based on the ASCII code.

Syntax : - 

CHAR(code)

CHARINDEX() Function : - 

CHARINDEX() function in SQL search for the substring in a string and returns the position. If any substring not founded then returns 0.

Syntax : - 

CHARINDEX(substring, 
  string, start)

  SQL server CONCAT() function : -

CONCAT() method in SQL use to add two or more string together.

Syntax : - 

CONCAT(string1, 
  string2, ...., string_n)

SQL Server Concat With + : -

The + operator also works like the Concat() method for adding two or more strings together.

Syntax : - 

string1 + string2 + string_n

CONCAT_WS() Function in SQL server: - 

CONCAT_WS() function in SQL use to add two or more string together with a separator.

Syntax : - 

CONCAT_WS(separator, string1,string2, ...., string_n)

DATALENGTH() Function in SQL server: - 

DATALENGTH() function in SQL gives the number of bytes used to represent an expression.

Syntax : - 

DATALENGTH(expression)

 DIFFERENCE() Function in SQL server: - 

 DIFFERENCE() for SQL compares two SOUNDEX( four-character code) values and returns an integer. Integer value indicates the match for the two SOUNDEX values, from 0 to 4. compares two SOUNDEX values.

Syntax : - 

DIFFERENCE(expression, expression)

 FORMAT() Function in SQL server: - 

 FORMAT() for SQL function formats a value with the specified format.

Syntax : - 

FORMAT(value, format, culture)

LEFT() Function in SQL server: - 

 LEFT() method in SQL extracts a number of characters from a string that is starting from left.

Syntax : - 

LEFT(string, number_of_chars)

LEN() function of SQL : - 

 LEN() function from the SQL server returns the length of a string but the space form the string is not included.

Syntax : - 

LEN(string)

LOWER() function of SQL : - 

LOWER() function in SQL returns converts a string to lower-case.

Syntax : - 

LOWER(text)

LTRIM() function: - 

LTRIM() function used to removes leading spaces from a string.

Syntax : - 

LTRIM(string)

NCHAR() function from SQL: - 

NCHAR() function of SQL return the Unicode character on the basis of number code.

Syntax : - 

NCHAR(number_code)

PATINDEX() function from SQL: - 

PATINDEX() function gives you the position (address) of a pattern in a string. If the pattern is not found then return zero.

Syntax : - 

PATINDEX(%pattern%, string)

    % - Match any string of any length (including 0 lengths)
    _ - Match an only single character
    [] - Match any characters within the brackets, e.g. [xyz]
    [^] - Match any character, which is not in the brackets, e.g. [^xyz].

QUOTENAME() function from SQL server: - 

QUOTENAME() function in SQL returns the Unicode string with delimiters added for making that string a valid SQL Server delimited identifier.

Syntax : - 

QUOTENAME(string, quote_char)

REPLACE() in SQL: - 

REPLACE() method in SQLreplace all the substring within a string, with a new substring and search is case-insensitive.

Syntax : - 

REPLACE(string, old_string, new_string)

REPLICATE() in SQL: - 

REPLICATE() Function in SQL repeats a string a specified number of times.

Syntax : - 

REPLICATE(string, integer)

REVERSE() in SQL: - 

REVERSE() Function in SQL gives you a reverse string as the result.

Syntax : - 

REVERSE(string)

REVERSE() in SQL: - 

REVERSE() Function in SQL gives you a reverse string as the result.

Syntax : - 

REVERSE(string)

RTRIM() in SQL: - 

RTRIM() in SQL removes all space characters from the right-hand side of a string.

Syntax : - 

RTRIM( string )

SOUNDEX() server function in SQL: - 

SOUNDEX() in SQL find a four-character code so that it evaluates the similarity of two expressions.

Syntax : - 

SOUNDEX(expression)

SPACE() server function in SQL: - 

SPACE() method in SQL returns a string with a specified number of spaces.

Syntax : - 

SPACE(number)

STR() server function in SQL: - 

STR() in SQL function returns a string that represents a number.

Syntax : - 

STR(number, length, decimals)

STUFF() server function in SQL: - 

STUFF() function allow you to edit the string with deletes a part of a string and then inserts another part into the string starting point specified by the user.

Syntax : - 

STUFF(string, start, length, new_string)

SUBSTRING() in SQL: - 

SUBSTRING() is a part of the string and this function extracts some characters from a string.

Syntax : - 

SUBSTRING(string, start, length)

SUBSTRING() in SQL: -

SUBSTRING() is a part of the string and this function extracts some characters from a string.

Syntax : - 

SUBSTRING(string, start, length)

TRIM() in SQL: -

TRIM() method in SQL removes the space character OR other specified characters from the start or end of a string. There are two more kinds of method to trim LTRIM() and RTRIM() functions.

Syntax : - 

TRIM([characters FROM ]string)

TRIM() in SQL: -

TRIM() method in SQL removes the space character OR other specified characters from the start or end of a string. There are two more kinds of method to trim LTRIM() and RTRIM() functions.

Syntax : - 

TRIM([characters FROM ]string)

UNICODE in SQL: -

UNICODE() in SQL gives the first character from the first character of the input expression.

Syntax : - 

UNICODE(character_expression)

UNICODE in SQL: -

UPPER() function in SQL helps you to changes a string to the upper-case.

Syntax : - 

UPPER(text)

Read More

Learn about the miscellaneous python function

Miscellaneous function in python:-

These all are the miscellaneous python function these functions are used some times or on the place of other functions. Some of them are removed from python 3 or some don't work on python 2 that's why I put them in the miscellaneous category.

Learn about the miscellaneous python function

reduce() Function : - 

reduce() function in python take the function and a sequence then returns a single value calculated as : - 

Step 1:- reduce function called for the two items from the sequence and gives the result.

Step 2: -  function called again for the two items from the sequence and the element will be the result which is obtained from step 1 and the next value in the sequence. This process keeps going until there are items in the sequence.

Syntax : - 

reduce(function, sequence[, initial]) -> value

NOTE: - if you want to use it, you have to first import it.

 strftime method in python : - 

strftime() function in python helps you to convert date, time, and datetime object to its equivalent string.

strftime() returns a string that represents the date and time using date, time, or datetime object. 

example : -

from datetime import datetime

now = datetime.now() # current date and time

year = now.strftime("%Y")
print("year:", year)

month = now.strftime("%m")
print("month:", month)

day = now.strftime("%d")
print("day:", day)

time = now.strftime("%H:%M:%S")
print("time:", time)

date_time = now.strftime("%m/%d/%Y, %H:%M:%S")
print("date and time:",date_time) 

timeit() method in Python:-

timeit() function in python is used to measuring the execution time of your python code snippets.it is an in-built python library timeit and it is a simple method of the execution time of small bits of Python code.

# importing the required module 
import timeit 
  
# code snippet to be executed only once 
mysetup = "from math import sqrt"
  
# code snippet whose execution time is to be measured 
mycode = ''' 
def example(): 
    mylist = [] 
    for x in range(100): 
        mylist.append(sqrt(x)) 
'''
  
# timeit statement 
print timeit.timeit(setup = mysetup, 
                    stmt = mycode, 
                    number = 10000) 

Python - Map Function : - 

map() function in python is a build-in function and it called a function for a specified iterable and gives a list of results.

example:- 

def square(x):
    return x*x

Syntax : -

map(function, iterable [, iterable2, iterable3,...iterableN]) --> map object

Python main for function : -

main() function is an execution point for a program file some programming language has a function special function called main() which tells about the execution point. But Python runs each line serially from the top of the file and has no explicit main() function.

Python has another way to tell about the execution point that is __name__ property of in python file.

__name__variable shows the name of the current module. The value will depend on where we execute the Python file. 

example: -

import helloworld
def main():
    print("Hello World")

if __name__=="__main__":
    main()

mkdir() method in python : - 

If any user wants to interact with the operating system in python so python provides OS module. This module provides a portable way in python for operating system dependent functionality.

Function in os module raises OSError in the case of invalid or inaccessible file names and paths.

os.makedirs() function used to create the create a directory recursively or if any directory is missing then os.makedirs() function create them all.

# Python program to explain os.makedirs() method  
    
# importing os module  
import os 
  
# Leaf directory 
directory = "known"
  
# Parent Directories 
parent_dir = "/home/User/Documents/functioninprogramming/Authors"
  
# Path 
path = os.path.join(parent_dir, directory) 
  
# Create the directory 
# 'known' 
os.makedirs(path) 
print("Directory '%s' created" %directory) 
  
# Directory 'functioninprogramming' and 'Authors' will 
# be created too  
# if it does not exists 
  
  
  
# Leaf directory 
directory = "c"
  
# Parent Directories 
parent_dir = "/home/User/Documents/functioninprogramming/a/b"
  
# mode 
mode = 0o666
  
path = os.path.join(parent_dir, directory) 
  
# Create the directory 
# 'c' 
   
os.makedirs(path, mode) 
print("Directory '%s' created" %directory) 

Python raw_input method : -

raw_input function in python is used to read a string from standard input such as a keyboard and with the help of this method user can insert data into a program. 

Example: - 

print "What is your name?"
name = raw_input()
print "Hello %s!" % name 

factorial() function in Python : -

Python provides factorial() function that can be used to compute the factorial of a number without writing the whole code for factorials like in C or C++.

# Python code to demonstrate math.factorial() 
import math 
  
print ("The factorial of 17 is : ", end="") 
print (math.factorial(17)) 

ceil() function in Python : -

ceil method in python gives the ceiling value if x means the smallest integer not less than x.

# This will import math module
import math   
  
# prints the ceil using ceil() method
print "math.ceil(-23.11) : ", math.ceil(-23.11) 

  Split() function in python : - 

Well, all we know about joining two strings in Python by concatenation but split() function does the exact opposite of that.

split() function scans through a string and separates them whenever the separator called for it.

Syntax: -

str.split(separator, maxsplit)

Read More

python built-in function for file handling

Python File Method : - 

These all are the python built-in function which is used to manipulate the python files and update these files in python.

python built-in function for file handling

close() method in python : - 

close() method in python closes an open file. A file should always be closed because without closing file changes made to a file may not show until you close the file.

Syntax : -

file.close() 

Python File fileno() Method : - 

fileno() function in python returns the file descriptor of the stream, as a number, and an error will occur if the operating system does not use a file descriptor.

Syntax : -

file.fileno()
 

Python flush() Method : - 

flush() function solve the buffer problem this function  cleans out the internal buffer.

Syntax : -

file.fileno()

isatty() function in python : - 

isatty() function in python returns True if the file stream is interactive, example: connected to a terminal.

Syntax : -

file.isatty()

read() function in python : - 

read() method in python gives the number of bytes of the specified file, Default is -1 it means the whole file.

Syntax : -

file.read()

readable() function in python : - 

readable() function in python gives true if the file is readable,or false if the file is not readable.

Syntax : -

file.readable()

readline() function in python : - 

readline() in python returns one line from the file. The programmer can also decide how many bytes from the line to return.

Syntax : -

file.readline(size)

readlines() method in python : - 

readlines() method in python gives a list that contains each line in the file as a list item. You can use the hint parameter to limit the number of lines returned.

Syntax : -

file.readlines(hint)

seek() method in python : - 

seek() method in python set the current file position in a file stream and return the new position of the file.

Syntax : -

file.seek(offset)

seekable() method in python : - 

seekable() in python method returns True if the file is seekable, False if not. A file is seekable if it allows access to the file stream in python.

Syntax : -

file.seekable()

tell() function in python : - 

tell() method in python gives the current file position in a file stream and the file position can be changed with the help of seeks function in python.

Syntax : -

file.tell()

truncate() function in python : - 

truncate() method in python use to resize the file to the given number of bytes.
 If the size is not specified, the current position will be used.

Syntax : -

file.truncate(size)

writable() function in python : - 

writable() method in python gives true if a file is writable and a file is writable so it is opened using "a" for append or "w" for write.

Syntax : -

file.writable()

Python File write() Method

write method in python is used to write specified text into the file.

Where this text will be inserted depends on the file mode and stream position.

"a":  The text will be inserted at the current file stream position, default at the end of the file.

"w": The file will be emptied before the text will be inserted at the current file stream position, and the default will be 0. 

Syntax : -

file.write(byte)

writelines() Method

writelines() function in python writes the items of a list into the specified file.

Where this item will be inserted depends on the file mode and stream position.

"a":  The item will be inserted at the current file stream position, default at the end of the file.

"w": The file will be emptied before the list item will be inserted at the current file stream position, and the default will be 0.

Syntax : -

file.writelines(list)

Read More

Python In Build Function To Manipulate On Python Set

SET FUNCTION IN PYTHON PROGRAMMING 

 These all are the in-build function in python to manipulate on python set. These functions will reduce the time and program complexity.

Python In Build Function To Manipulate On Python Set

add() function in python: - 

add() method add the element to the python set. If the element already exists, then add() function does not add the element to the python set.

Syntax : -

set.add(elmnt) 

clear() function in python: -

add() method removes all the elements from the specified python set.

Syntax : -

set.clear()

copy() function in python: -

copy() function copies the set in python programming.

Syntax : -

set.copy()

difference() function in python: -

difference() function returns a set that contains the difference between two sets.

Syntax : -

set.difference(set)

it can also be shown as A ∩ B set A has some element that doesn't exist in set B.

difference update() function in python: - 

difference_update() function in python removes the common element in both sets. it gives you a new without unwanted elements.

Syntax : -

set.difference_update(set)

discard() function in python: -

discard() function in python remove the specified element for the set in python.

Syntax : -

set.discard(value) 

intersection() function in python: - 

As set theory says intersection() function gives a set that contains the similarity between two or more sets.

Syntax : -

set.intersection(set1, set2 ... etc)

intersection update() function in python: -

intersection_update() function in python removes the items that are not present in both sets.

Syntax : -

set.intersection_update(set1, set2 ... etc)

isdisjoint() function: -

isdisjoint() in python gives true if none of the items are present in both sets, otherwise, it returns False.

Syntax : -

set.isdisjoint(set)

issubset(): -

issubset() method for python returns True if all items in the set exist in the specified set otherwise it returns False.

Syntax : -

set.issubset(set)

 issuperset() Method for python set :-

issuperset() method for python returns True if all items of specified set exist in the original set otherwise it returns False.

Syntax : -

set.issuperset(set)

 pop() Method for python set:-

pop() function removes a random item from the set and returns the removed item.

Syntax : -

set.pop()

remove() python set:-

 remove() function removes the specified element from the set and raise an error if the specified item does not exist.

Syntax : -

set.remove(item)

symmetric_difference():-

symmetric_difference() method returns a set that contains all items from both sets, but not the items that are present in both sets.

Syntax : -

set.symmetric_difference(set)

symmetric_difference_update():-

symmetric_difference_update() method in python set function updates the original set by removing items that are present in both sets and inserting the other items.

Syntax : -

set.symmetric_difference_update(set)

Python Set union() Method:-

According to the math set theory union(), method returns a set that contains all items from the original set, and also all elements from the specified sets.

Syntax : -

set.union(set1, set2...)

update() Method in python:-

update() python set function update the set by adding items from another set. If one item is present in both sets, only one appearance of this item will be present in the updated set.

Syntax : -

set.union(set1, set2...)

Read More

Python Dictionary Method's and python built-in function

Python Dictionary Method's

These are all the built-in methods that you can use to manipulate the python dictionary.

Python Dictionary Method's and python built-in function

clear() function in python:- 

clear() method in python dictionary use to remove all elements from a dictionary.

Syntax : -

dictionary.clear() 

copy() method in python dictionary:-

As its name says copy() method gives a copy of the specified dictionary.

Syntax : -

dictionary.copy()

fromkeys() Method : - 

formkeys() method helps to return a python dictionary with specified keys and the specified value.

Syntax : -

dict.fromkeys(keys, value)

get() Method : -  

get() method helps to return the value of the item with the specified key.

Syntax : -

dictionary.get(keyname, value)

items() Method : -  

items() in python dictionary gives the view object that contains key-value pairs of the dictionary, and reflect any changes made to the dictionary

Syntax : -

dictionary.items()

key() Method : -  

key() in python dictionary returns the view object that contains the key of the dictionary as a list and reflects any changes made to the dictionary.

Syntax : -

dictionary.keys()

pop() Method in python: -  

pop() in python follows the same method like remove() function the only difference is pop() function remove the specified item from the dictionary.

Syntax : -

dictionary.pop(keyname, defaultvalue)

remove item will be the return value of pop() function.

popitem() Method in python: -  

popitem() in python dictionary removes the last inserted item into the dictionary.  

Syntax : -

dictionary.popitem()

removed value in popitem() will be the return value in a python dictionary.

Python Dictionary setdefault() Method

setdefault() method gives/returns the value from python dictionary the item with the specified key. If any key does not exist, insert the key, with the specified value.

Syntax : -

dictionary.setdefault(keyname, value)

Python Dictionary update() Method:-

update() method use to inserts the specified items to the python dictionary.

Syntax : -

dictionary.update(iterable)

 values() Method in python dictionary:-

values() method used to return the view object that contains the values of the dictionary, as a list.

Syntax : -

dictionary.values()

Reflect any changes make to the python dictionary

Read More

build-in function for list in python and array method

These all are the build-in function which is used to manipulate the list in the python.

build-in function for list in python and array method

Python List append Method:-

append() function in python use to add an element at the end of the list.

Syntax : - 

list.append(elmnt) 

clear() method in Python:-

clear() function in python programming helps you to remove all the elements from the list

Syntax : - 

list.clear()

copy() method in list:-

As its name says copy() function gives a copy of the specified list.

Syntax : - 

list.copy()

count() method in python list:-

count() function in python count the number of elements in a specified value and return it.

Syntax : - 

list.count(value)

extend() method in python list:-

extend() method of python list uses to add any specified iterable at the end of the current list.

Syntax : - 

list.extend(iterable)

index() method in python:-

index() method of list returns the address of the first occurrence of the specified value in python.

Syntax : - 

list.index(elmnt)

insert() function in python:-

insert() function uses to insert an element at the specified position in the python list.

Syntax : - 

list.insert(pos, elmnt)

pop() function in python list:-

pop() function uses as it is used in the data structure to remove an element from a list at the specified position so it uses the same in python to remove any element from the python list.

Syntax : - 

list.pop(pos)

the default value is -1 which gives the last element of a list.

remove() function in python list:-

remove() method of python list removes the first occurrence of the element at the specified value.

Syntax : - 

list.remove(elmnt)

reverse() method in python list:-

reverse() method of python list helps to reverse the sorting order of the element in the list.

Syntax : - 

list.reverse()

sort() method:-

sort() method of python list helps to sort the list in ascending by default. 

Syntax : - 

list.sort(reverse=True|False, key=myFunc)

Read More