Conversion functions in SQL

 These all are the function in SQL which is used to converts an expression from one data type to another data type and these all function are known as Conversion "Functions in SQL".

 

Conversion functions in SQL

CAST function in SQL : - 

CAST in SQL helps to converts an expression from one data type to another data type. If the conversion fails, CAST function would come back with a mistake. Otherwise, it'll give the conversion value.

Syntax  : - 

CAST( expression AS type [ (length) ] )

length: - length of the resulting data type like char, int, float, etc.

Example : - 

SELECT CAST(13.35 AS int);
Result: 13          (result is truncated)

SELECT CAST(14.56 AS float);
Result: 14.56       (result is not truncated)

SELECT CAST(14.7 AS varchar);
Result: '14.7'

SELECT CAST(149.6 AS varchar(4));
Result: '15.9'

SELECT CAST('15.6' AS float);
Result: 15.6

SELECT CAST('2014-05-02' AS datetime);
Result: '2014-05-02 00:00:00.000'

CONVERT function in SQL : -

CONVERT method in SQL does the same as CAST function "converts an expression from one data type to another data type."
But the syntax is different in this method tell the type of data type in which you wish to convert the expression to and type used to tell about the format used to convert between data types.

Syntax  : - 

CONVERT( type [ (length) ], expression [ , style ] )

Example : -

SELECT CONVERT(int, 13.85);
Result: 13          (result is truncated)

SELECT CONVERT(float, 13.85);
Result: 13.85       (result is not truncated)

SELECT CONVERT(varchar, 15.7);
Result: '15.7'

SELECT CONVERT(varchar(4), 15.7);
Result: '15.7'

SELECT CONVERT(float, '15.6');
Result: 15.6

SELECT CONVERT(datetime, '2014-05-02');
Result: '2014-05-02 00:00:00.000'

SELECT CONVERT(varchar, '05/02/2014', 101);
Result: '05/02/2014'

TRY_CAST function in SQL Server : -

TRY_CAST function also does the same work as the previous two functions did but this function will return NULL for any error. Otherwise, it will return the converted value.

Syntax  : - 

TRY_CAST( expression AS type [ (length) ] )

Example : -

SELECT TRY_CAST(13.85 AS int);
Result: 13
(result is a truncated int value)

SELECT TRY_CAST(13.85 AS float);
Result: 13.85
(returned as a float value and is not truncated)

SELECT TRY_CAST('14 Main St.' AS float);
Result: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)

SELECT TRY_CAST(15.6 AS varchar);
Result: '15.6'
(result is returned as varchar)

SELECT TRY_CAST(15.6 AS varchar(2)); 
Result: NULL
(result is NULL because conversion failed since the value will not fit in a 2 character varchar)

SELECT TRY_CAST('2018-09-13' AS datetime);
Result: '2018-09-13 00:00:00.000'
(result is returned as a datetime)

TRY_CONVERT function in SQL Server : -

TRY_CONVERT function could be used at the place of CONVERT function in SQL because this function also used to "converts an expression from one data type to another data type." and TRY_CONVERT function will return NULL for any error. Otherwise, it will return the converted value.

Syntax  : - 

TRY_CONVERT( type [ (length) ], expression [ , style ] )

Example : -

SELECT TRY_CONVERT(int, 14.80);
Result: 14


SELECT TRY_CONVERT(float, 14.75);
Result: 14.75
(result is returned as a float value and is not truncated)

SELECT TRY_CONVERT(float, '14 Main St.');
Result: NULL


SELECT TRY_CONVERT(varchar, 15.6);
Result: '15.6'
(result is returned as a varchar)

SELECT TRY_CONVERT(varchar(2), 15.6); 
Result: NULL


SELECT TRY_CONVERT(datetime, '2018-09-13');
Result: '2018-09-13 00:00:00.000'


SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
Result: '09/13/2018'
(result is returned as a varchar with a style of 101 - mm/dd/yyyy (US standard) )

Read More

Read about Time/Date function in SQL

Learn about all date function which is used to manipulate in the SQL server with the help of Date function in SQL for example - CURRENT_TIMESTAMP function in SQL, DATEADD, DATENAME.

Read about Time/Date function in SQL

CURRENT_TIMESTAMP function in SQL : -

SQL gives a function to the user which helps to return the current date and time in the SQL server and that function is known as CURRENT_TIMESTAMP function.

Syntax : - 

CURRENT_TIMESTAMP

This function gives date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'.

See the example : - 

SELECT CURRENT_TIMESTAMP;
Result: '2014-05-01 14:19:55.918'

DATEADD function in SQL : -

DATEADD function in SQL helps to give a date after adding or subtracting the interval from the date.

dateadd function in SQL

Syntax:- 

DATEADD( interval, number, date )

Example : -

SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'

SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'
SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'
SELECT DATEADD(year, -1, '2014/03/23');
Result: '2013-03-23 00:00:00.000'

SELECT DATEADD(month, 1, '2014/09/18');
Result: '2014-09-18 00:00:00.000'

SELECT DATEADD(month, -1, '2014/04/28');
Result: '2014-03-28 00:00:00.000'

SELECT DATEADD(day, 1, '2014/04/28');
Result: '2014-04-29 00:00:00.000'

SELECT DATEADD(day, -1, '2014/04/28');
Result: '2014-04-27 00:00:00.000'

DATEDIFF function in SQL : -

DATEDIFF function tells about the between two date values, based on the interval specified.

datediff function in SQL

Syntax:- 

DATEDIFF( interval, date1, date2 )

The interval used to calculate the difference between date1 and date2.

Example : - 

SELECT DATEDIFF(year, '2012/01/18', '2014/01/28');
Result: 2

SELECT DATEDIFF(yyyy, '2010/04/28', '2012/04/28');
Result: 2

SELECT DATEDIFF(yy, '2010/04/28', '2012/04/28');
Result: 2

SELECT DATEDIFF(month, '2014/01/01', '2014/04/28');
Result: 3

SELECT DATEDIFF(day, '2014/01/01', '2014/04/28');
Result: 117

SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 2

SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 165

DATENAME in SQL : -

DATENAME function in SQL gives the specified part of the of a given date, as a string value

Syntax:- 

DATENAME( interval, date )

it will return the result in the string.

Example : - 

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2012/04/28');
Result: '2012'

SELECT DATENAME(yy, '2010/04/28');
Result: '2010'

SELECT DATENAME(month, '2014/01/28');
Result: 'Jan'

SELECT DATENAME(day, '2014/04/19');
Result: '19'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'

DATEPART in SQL : -

DATEPART function in SQL gives the specified part of the of a given date, as an integer value.

Syntax:- 

DATEPART( interval, date )

it will return the result in the integer.

Example : - 

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2012/04/28');
Result: '2012'

SELECT DATENAME(yy, '2010/04/28');
Result: '2010'

SELECT DATENAME(month, '2014/01/28');
Result: '1'

SELECT DATENAME(day, '2014/04/19');
Result: '19'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'

DATEPART in SQL : -

DAY function tells about the day of the month (between 1 to 31) given a date value.

Syntax:- 

DAY( date_value )

Example : - 

SELECT DAY('2014/04/22');
Result: 22

SELECT DAY('2014/03/23 10:05');
Result: 23

SELECT DAY('2014/04/11 10:05:18.621');
Result: 11

GETDATE SQL function : -

GATEDATE function in SQL gives you the current date and time just like the CURRENT_TIMESTAMP function.

Syntax:- 

GETDATE ( )

Example : - 

SELECT GETDATE();
Result: '2014-04-28 18:17:28.160'

GETUTCDATE SQL function : -

GETUTCDATE function for SQL server returns the current UTC date and time.

Syntax:- 

GETUTCDATE ( )

Example : - 

SELECT GETUTCDATE();
Result: '2014-04-29 00:27:58.657' 

MONTH Function in SQL : -

MONTH function tells about the MONTH of the month (between 1 to 12) given a date value.

Syntax:- 

MONTH( date_value )

Example : - 

SELECT MONTH('2014/02/28');
Result: 2

SELECT MONTH('2014/01/31 10:05');
Result: 1

SELECT MONTH('2014/10/01 10:05:18.621');
Result: 10

MONTH Function in SQL : -

MONTH function tells about the four-digit year (in number) given a date value.

Syntax:- 

YEAR( date_value )

Example : - 

SELECT YEAR('2012/04/28');
Result: 2012

SELECT YEAR('2009/03/31 10:05');
Result: 2009

SELECT YEAR('2011/12/01 10:05:18.621');
Result: 2011

Read More

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