Welcome To SUNTEC Tutorial Point - www.sunteccampus.com

  • SUNTEC

    SUNTEC Computer college

  • Computer Programming

    Computer Programming

  • Web Development

    Web Development

  • Computer Accounting

    Computer Accounting

  • SUNTEC Graphic Designing

    Graphic designing

Home » » SQL

SQL

SQL - Wildcard Operators

We have already discussed about the SQL LIKE operator, which is used to compare a value to similar values using the wildcard operators.
SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table.
Sr.No.Wildcard & Description
1
The percent sign (%)
Matches one or more characters.
Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.
2
The underscore (_)
Matches one character.
Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.
The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Syntax

The basic syntax of a '%' and a '_' operator is as follows.
SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using the AND or the OR operators. Here, XXXX could be any numeric or string value.

Example

The following table has a number of examples showing the WHERE part having different LIKE clauses with '%' and '_' operators.
Sr.No.Statement & Description
1
WHERE SALARY LIKE '200%'
Finds any values that start with 200.
2
WHERE SALARY LIKE '%200%'
Finds any values that have 200 in any position.
3
WHERE SALARY LIKE '_00%'
Finds any values that have 00 in the second and third positions.
4
WHERE SALARY LIKE '2_%_%'
Finds any values that start with 2 and are at least 3 characters in length.
5
WHERE SALARY LIKE '%2'
Finds any values that end with 2.
6
WHERE SALARY LIKE '_2%3'
Finds any values that have a 2 in the second position and end with a 3.
7
WHERE SALARY LIKE '2___3'
Finds any values in a five-digit number that start with 2 and end with 3.
Let us take a real example, consider the CUSTOMERS table having the following records.
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
The following code block is an example, which would display all the records from the CUSTOMERS table where the SALARY starts with 200.
SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';
This would produce the following result.
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - Date Functions

The following table has a list of all the important Date and Time related functions available through SQL. There are various other functions supported by your RDBMS. The given list is based on MySQL RDBMS.
Sr.No.Function & Description
1ADDDATE()
Adds dates
2ADDTIME()
Adds time
3CONVERT_TZ()
Converts from one timezone to another
4CURDATE()
Returns the current date
5CURRENT_DATE(), CURRENT_DATE
Synonyms for CURDATE()
6CURRENT_TIME(), CURRENT_TIME
Synonyms for CURTIME()
7CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
Synonyms for NOW()
8CURTIME()
Returns the current time
9DATE_ADD()
Adds two dates
10DATE_FORMAT()
Formats date as specified
11DATE_SUB()
Subtracts two dates
12DATE()
Extracts the date part of a date or datetime expression
13DATEDIFF()
Subtracts two dates
14DAY()
Synonym for DAYOFMONTH()
15DAYNAME()
Returns the name of the weekday
16DAYOFMONTH()
Returns the day of the month (1-31)
17DAYOFWEEK()
Returns the weekday index of the argument
18DAYOFYEAR()
Returns the day of the year (1-366)
19EXTRACT
Extracts part of a date
20FROM_DAYS()
Converts a day number to a date
21FROM_UNIXTIME()
Formats date as a UNIX timestamp
22HOUR()
Extracts the hour
23LAST_DAY
Returns the last day of the month for the argument
24LOCALTIME(), LOCALTIME
Synonym for NOW()
25LOCALTIMESTAMP, LOCALTIMESTAMP()
Synonym for NOW()
26MAKEDATE()
Creates a date from the year and day of year
27MAKETIME
MAKETIME()
28MICROSECOND()
Returns the microseconds from argument
29MINUTE()
Returns the minute from the argument
30MONTH()
Return the month from the date passed
31MONTHNAME()
Returns the name of the month
32NOW()
Returns the current date and time
33PERIOD_ADD()
Adds a period to a year-month
34PERIOD_DIFF()
Returns the number of months between periods
35QUARTER()
Returns the quarter from a date argument
36SEC_TO_TIME()
Converts seconds to 'HH:MM:SS' format
37SECOND()
Returns the second (0-59)
38STR_TO_DATE()
Converts a string to a date
39SUBDATE()
When invoked with three arguments a synonym for DATE_SUB()
40SUBTIME()
Subtracts times
41SYSDATE()
Returns the time at which the function executes
42TIME_FORMAT()
Formats as time
43TIME_TO_SEC()
Returns the argument converted to seconds
44TIME()
Extracts the time portion of the expression passed
45TIMEDIFF()
Subtracts time
46TIMESTAMP()
With a single argument this function returns the date or datetime expression. With two arguments, the sum of the arguments
47TIMESTAMPADD()
Adds an interval to a datetime expression
48TIMESTAMPDIFF()
Subtracts an interval from a datetime expression
49TO_DAYS()
Returns the date argument converted to days
50UNIX_TIMESTAMP()
Returns a UNIX timestamp
51UTC_DATE()
Returns the current UTC date
52UTC_TIME()
Returns the current UTC time
53UTC_TIMESTAMP()
Returns the current UTC date and time
54WEEK()
Returns the week number
55WEEKDAY()
Returns the weekday index
56WEEKOFYEAR()
Returns the calendar week of the date (1-53)
57YEAR()
Returns the year
58YEARWEEK()
Returns the year and week

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)
When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr.
mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the result. The expr1 is a time or datetime expression, while the expr2 is a time expression.
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ(dt,from_tz,to_tz)

This converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')           |
+---------------------------------------------------------+
| 2004-01-01 13:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')     |
+---------------------------------------------------------+
| 2004-01-01 22:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURDATE()

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or in a numeric context.
mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE()                                               |
+---------------------------------------------------------+
| 1997-12-15                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0                                           |
+---------------------------------------------------------+
| 19971215                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_DATE and CURRENT_DATE()

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()

CURTIME()

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or in a numeric context. The value is expressed in the current time zone.
mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME()                                               |
+---------------------------------------------------------+
| 23:50:26                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0                                           |
+---------------------------------------------------------+
| 235026                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_TIME and CURRENT_TIME()

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

DATE(expr)

Extracts the date part of the date or datetime expression expr.
mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
|  2003-12-31                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 . expr2 expressed as a value in days from one date to the other. Both expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30')            |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform date arithmetic. The date is a DATETIME or DATE value specifying the starting date. The expr is an expression specifying the interval value to be added or subtracted from the starting date. The expr is a string; it may start with a '-' for negative intervals.
unit is a keyword indicating the units in which the expression should be interpreted.
The INTERVAL keyword and the unit specifier are not case sensitive.
The following table shows the expected form of the expr argument for each unit value.
unit ValueExpected exprFormat
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND'MINUTES.MICROSECONDS'
MINUTE_SECOND'MINUTES:SECONDS'
HOUR_MICROSECOND'HOURS.MICROSECONDS'
HOUR_SECOND'HOURS:MINUTES:SECONDS'
HOUR_MINUTE'HOURS:MINUTES'
DAY_MICROSECOND'DAYS.MICROSECONDS'
DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE'DAYS HOURS:MINUTES'
DAY_HOUR'DAYS HOURS'
YEAR_MONTH'YEARS-MONTHS'
The values QUARTER and WEEK are available from the MySQL 5.0.0. version.
mysql> SELECT DATE_ADD('1997-12-31 23:59:59', 
   -> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL...             |
+---------------------------------------------------------+
| 1998-01-01 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR)                 |
+---------------------------------------------------------+
| 1999-01-01 01:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT(date,format)

This command formats the date value as per the format string.
The following specifiers may be used in the format string. The '%' character is required before the format specifier characters.
Sr.No.Specifier & Description
1
%a
Abbreviated weekday name (Sun..Sat)
2
%b
Abbreviated month name (Jan..Dec)
3
%c
Month, numeric (0..12)
4
%D
Day of the month with English suffix (0th, 1st, 2nd, 3rd, .)
5
%d
Day of the month, numeric (00..31)
6
%e
Day of the month, numeric (0..31)
7
%f
Microseconds (000000..999999)
8
%H
Hour (00..23)
9
%h
Hour (01..12)
10
%I
Hour (01..12)
11
%i
Minutes, numeric (00..59)
12
%j
Day of year (001..366)
13
%k
Hour (0..23)
14
%l
Hour (1..12)
15
%M
Month name (January..December)
16
%m
Month, numeric (00..12)
17
%p
AM or PM
18
%r
Time, 12-hour (hh:mm:ss followed by AM or PM)
19
%S
Seconds (00..59)
20
%s
Seconds (00..59)
21
%T
Time, 24-hour (hh:mm:ss)
22
%U
Week (00..53), where Sunday is the first day of the week
23
%u
Week (00..53), where Monday is the first day of the week
24
%V
Week (01..53), where Sunday is the first day of the week; used with %X
25
%v
Week (01..53), where Monday is the first day of the week; used with %x
26
%W
Weekday name (Sunday..Saturday)
27
%w
Day of the week (0=Sunday..6=Saturday)
28
%X
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
29
%x
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
30
%Y
Year, numeric, four digits
31
%y
Year, numeric (two digits)
32
%%
A literal .%. character
33
%x
x, for any.x. not listed above
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y')          |
+---------------------------------------------------------+
| Saturday October 1997                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
   -> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00.......                 |
+---------------------------------------------------------+
|  22 22 10 10:23:00 PM 22:23:00 00 6                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_SUB(date,INTERVAL expr unit)

This is similar to the DATE_ADD() function.

DAY(date)

The DAY() is a synonym for the DAYOFMONTH() function.

DAYNAME(date)

Returns the name of the weekday for date.
mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05')                                   |
+---------------------------------------------------------+
| Thursday                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFMONTH(date)

Returns the day of the month for date, in the range 0 to 31.
mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03')                                |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard.
mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03')                                  |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366.
mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03')                                 |
+---------------------------------------------------------+
| 34                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

EXTRACT(unit FROM date)

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02')                         |
+---------------------------------------------------------+
| 1999                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03')          |
+---------------------------------------------------------+
| 199907                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FROM_DAYS(N)

Given a day number N, returns a DATE value.
mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669)                                       |
+---------------------------------------------------------+
| 1997-10-07                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Note − Use FROM_DAYS() with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582).

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context. The value is expressed in the current time zone. The unix_timestamp argument is an internal timestamp values, which are produced by the UNIX_TIMESTAMP() function.
If the format is given, the result is formatted according to the format string, which is used in the same way as is listed in the entry for the DATE_FORMAT() function.
mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580)                                |
+---------------------------------------------------------+
| 1997-10-04 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

HOUR(time)

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03')                                        |
+---------------------------------------------------------+
| 10                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Share this article :

Activities

Latest Post

SCRIPTS

DATABASES

BIG DATA & ANALYTICS

RELATIVE ARTICLE

 
Support : Copyright © 2014. SUNTEC CAMPUS TUTORIAL - All Rights Reserved
Site Designed by Creating Website Inspired Support
Proudly powered by Sun Microcreators