[go: up one dir, main page]
Scribd
Upload
37 views10 pages

DBMS 3

The document describes various string, numeric, and date functions in SQL. It provides examples of string functions like ASCII, CHAR_LENGTH, CONCAT, and UPPER. It also lists numeric functions such as ABS, CEIL, FLOOR, and GREATEST. Additionally, it covers date functions and conversions like NOW, CURDATE, TO_CHAR, and TO_DATE.

Uploaded by

pareshkumar3108
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
37 views10 pages

DBMS 3

The document describes various string, numeric, and date functions in SQL. It provides examples of string functions like ASCII, CHAR_LENGTH, CONCAT, and UPPER. It also lists numeric functions such as ABS, CEIL, FLOOR, and GREATEST. Additionally, it covers date functions and conversions like NOW, CURDATE, TO_CHAR, and TO_DATE.

Uploaded by

pareshkumar3108
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

Experiment : 3

Aim : Queries to facilitate acquaintance of Built-in Functions: String Functions,


Numeric Functions, Date Functions and Conversion Functions.

Description : String functions are used to perform an operation on input string and
return an output string. Following are the string functions defined in sql.
1. ASCII(): This function is used to find the ASCII value of a character.

2. CHAR_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL Server. This
function is used to find the length of a word.

3. CHARACTER_LENGTH(): This function is used to find the length of a line.

4. CONCAT(): This function is used to add two words or strings.


5. CONCAT_WS(): This function is used to add two words or strings with a symbol as
concatenating symbol.

6. FIND_IN_SET(): This function is used to find a symbol from a set of symbols.

7. LEFT(): This function is used to SELECT a sub string from the left of given size or
characters.

8. LOCATE(): This function is used to find the nth position of the given word in a string.

9. LOWER(): This function is used to convert the upper case string into lower case.
10. UPPER(): This function is used to convert the lower case string into upper case.

11. REVERSE(): This function is used to reverse a string.

12. RPAD(): This function is used to make the given string as long as the given size by
adding the given symbol on the right.

13. SUBSTR(): This function is used to find a sub string from the a string from the given
position.

14. REPEAT(): This function is used to write the given string again and again till the
number of times mentioned.
Numeric Functions are used to perform operations on numbers and return numbers.
Following are the numeric functions defined in SQL:
1. ABS(): It returns the absolute value of a number.

2. ACOS(): It returns the cosine of a number, in radians.

3. ASIN(): It returns the arc sine of a number, in radians.

4. CEIL(): It returns the smallest integer value that is greater than or equal to a number.

5. CEILING(): It returns the smallest integer value that is greater than or equal to a
number.
6. DIV(): It is used for integer division.

7. FLOOR(): It returns the largest integer value that is less than or equal to a number.

8. GREATEST(): It returns the greatest value in a list of expressions.

9. POWER(m, n): It returns m raised to the nth power.

10. ROUND(): It returns a number rounded to a certain number of decimal places.


In SQL, dates are complicated for newbies, since while working with a database, the
format of the data in the table must be matched with the input data to insert. In various
scenarios instead of date, datetime (time is also involved with date) is used.
For storing a date or a date and time value in a database, MySQL offers the following
data types:

DATE format YYYY-MM-DD

DATETIME format: YYYY-MM-DD HH:MI: SS

TIMESTAMP format: YYYY-MM-DD HH:MI: SS

YEAR format YYYY or YY

1. NOW(): Returns the current date and time.

2. CURDATE(): Returns the current date.

3. CURTIME(): Returns the current time.


When you define expressions and local variables then you should specify what type of
data will be stored in those objects such as text data, money, dates, numbers, or characters.
• Strings Data types such as CHAR and VARCHAR.
• Decimal values such as FLOAT and REAL.
• Binary String such as BINARY.
• Date and Time Data Types such as DATE, TIME, TIMESTAMP, and DATETIME.
• Numeric Data types such as INT, DOUBLE, and BIGINT.

Implicit Data-Type Conversion:


In this type of conversion, the data is converted from one type to another implicitly (by
itself/automatically).

From To

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

DATE VARCHAR2

NUMBER VARCHAR2
Here we see the output of both queries came out to be the same, in spite of the 2nd query
using ‘15000’ as text, it is automatically converted into an int data type.

Explicit Data-Type Conversion:


In this type of conversion, the data is converted from one type to another explicitly (by
the user). simply we can say, users define the type to which the expression is to be
converted.
1. TO_CHAR Function : TO_CHAR function is used to typecast a numeric or date input
to a character type with a format model (optional).
Syntax:

TO_CHAR(number1, [format], [nls_parameter])

SELECT first_name,
TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME HIRE_DATE SALARY


-------------------- ------------------ ----------
Steven JUNE 17, 2003 $24000.00
Neena SEPTEMBER 21, 2005 $17000.00
Lex JANUARY 13, 2001 $17000.00
Alexander JANUARY 03, 2006 $9000.00

The first TO_CHAR is used to convert the hire date to the date format MONTH DD,
YYYY i.e. month spelled out and padded with spaces, followed by the two-digit day of
the month, and then the four-digit year. If you prefer displaying the month name in mixed
case (that is, "December"), simply use this case in the format argument: ('Month DD,
YYYY').
The second TO_CHAR function in Figure 10-39 is used to format the SALARY to display
the currency sign and two decimal positions.
2. TO_NUMBER function : The TO_NUMBER function converts a character value to
a numeric datatype. If the string being converted contains nonnumeric characters, the
function returns an error.
Syntax:

TO_NUMBER (string1, [format], [nls_parameter])

SELECT TO_NUMBER('121.23', '9G999D99')


FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
121.23

SELECT TO_NUMBER('1210.73', '9999.99')


FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
1210.73

3. TO_DATE function : The function takes character values as input and returns
formatted date equivalent of the same. The TO_DATE function allows users to enter a
date in any format, and then it converts the entry into the default format used by Oracle
11g.
Syntax:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd,


YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

You might also like