A. SQL Reverse Lookup Reference
The following table lists the relevant SQL syntax organized according to purpose.
|
No. |
Category |
Purpose |
Relevant SQL syntax |
|---|---|---|---|
|
1 |
Data retrieval |
Retrieve data by specifying a range. |
|
|
2 |
Retrieve data that matches any of multiple values. |
||
|
3 |
Retrieve data that contains a specific character string. |
||
|
4 |
Retrieve data by using a regular expression |
||
|
5 |
Retrieve null-valued data. |
||
|
6 |
Eliminate duplication in the retrieval results. |
||
|
7 |
Sort retrieval results in ascending or descending order. |
||
|
8 |
Specify the maximum number of rows in the retrieval results. |
||
|
9 |
Re-use the same derived table within a SELECT statement. |
||
|
10 |
Change a column name in the retrieval results. |
||
|
11 |
Retrieve by specifying multiple branch conditions. |
||
|
12 |
Retrieve by joining multiple tables. |
||
|
13 |
Perform a subquery. |
||
|
14 |
|||
|
15 |
|||
|
16 |
|||
|
17 |
|||
|
18 |
Create the union of query results from multiple tables. |
||
|
19 |
Data deletion |
Delete all the rows in a base table. |
|
|
20 |
Delete all the rows in a chunk in a base table. |
||
|
21 |
Data aggregation |
Determine the sum of retrieved values. |
|
|
22 |
Determine the maximum value. |
||
|
23 |
Determine the minimum value. |
||
|
24 |
Determine the average of retrieved values. |
||
|
25 |
Determine the row count (number of results) |
||
|
26 |
|||
|
27 |
Determine the standard deviation of a population. |
||
|
28 |
Determine the standard deviation of a sample. |
||
|
29 |
Determine the variance of a population. |
||
|
30 |
Determine the variance of a sample. |
||
|
31 |
Determine the median of an ordered set of values. |
||
|
32 |
Determine the percentile of an ordered set of values. |
||
|
33 |
|||
|
34 |
Set a range in which to aggregate data. |
||
|
35 |
Aggregate the data into groups. |
||
|
36 |
|||
|
37 |
Character strings |
Check whether the target data contains character strings that meet the search condition expression. |
|
|
38 |
Concatenate two character string data items. |
||
|
39 |
|||
|
40 |
Remove specific characters from character string data. |
||
|
41 |
|||
|
42 |
|||
|
43 |
Extract a substring from character string data. |
||
|
44 |
|||
|
45 |
|||
|
46 |
Pad the beginning or end of character string data with any specified character string. |
||
|
47 |
|||
|
48 |
Replace any character string in the target data. |
||
|
49 |
Replace any character in character string data. |
||
|
50 |
Determine the number of characters in character string data. |
||
|
51 |
Search the target data for a character string and return the starting position of the string. |
||
|
52 |
Convert uppercase letters to lowercase. |
||
|
53 |
Convert lowercase letters to uppercase. |
||
|
54 |
Binary data |
Concatenate two binary data items. |
|
|
55 |
Extract a substring from binary data. |
||
|
56 |
Determine the value resulting from shifting the bits of a binary data value to the left. |
||
|
57 |
Determine the value resulting from shifting the bits of a binary data value to the right. |
||
|
58 |
Determine the bitwise logical AND of two binary data items. |
||
|
59 |
Determine the bitwise inclusive OR of two binary data items. |
||
|
60 |
Determine the bitwise logical NOT of a binary data item. |
||
|
61 |
Determine the bitwise exclusive OR of two binary data items. |
||
|
62 |
Convert binary data to a binary string representation (character string data consisting of 0 and 1). |
||
|
63 |
Convert binary data to a hex string representation (character string data consisting of 0 to 9, and A to F). |
||
|
64 |
Numerical calculations |
Determine the remainder after a division. |
|
|
65 |
Determine the absolute value. |
||
|
66 |
Determine the square root. |
||
|
67 |
Determine the sign of the data (positive, negative, or 0). |
||
|
68 |
Determine pseudorandom numbers that follow a uniform distribution and are greater than or equal to the value specified for the minimum value and less than the value specified for the maximum value.# |
||
|
69 |
|||
|
70 |
|||
|
71 |
Determine pseudorandom numbers that follow a normal distribution with an average μ, and a standard deviation σ. |
||
|
72 |
Rounding |
Round off a numeric value. |
|
|
73 |
Truncate a numeric value. |
||
|
74 |
Determine the greatest integer that is equal to or less than the specified numeric value. |
||
|
75 |
Determine the smallest integer that is equal to or greater than the specified numeric value. |
||
|
76 |
Exponent and logarithm |
Determine a power of the specified data. |
|
|
77 |
Determine the logarithm of the specified antilogarithm and base. |
||
|
78 |
Determine the natural logarithm. |
||
|
79 |
Determine a power of the base of the natural logarithm. |
||
|
80 |
Trigonometric functions |
Determine the sine (SIN trigonometric function). |
|
|
81 |
Determine the cosine (COS trigonometric function). |
||
|
82 |
Determine the tangent (TAN trigonometric function). |
||
|
83 |
Determine the inverse sine (inverse trigonometric function). |
||
|
84 |
Determine the inverse cosine (inverse trigonometric function). |
||
|
85 |
Determine the inverse tangent (inverse trigonometric function). |
||
|
86 |
|||
|
87 |
Determine the hyperbolic sine. |
||
|
88 |
Determine the hyperbolic cosine. |
||
|
89 |
Determine the hyperbolic tangent. |
||
|
90 |
Convert an angle from radians to degrees. |
||
|
91 |
Convert an angle from degrees to radians. |
||
|
92 |
Determine the value of |
||
|
93 |
Date and time |
Extract a portion of a date or time (for example, extract only the month). |
|
|
94 |
Given a date, determine the ordinal number of the date in the year. |
||
|
95 |
Given a date, determine what day of week it falls on as an ordinal number from the first day in the week. |
||
|
96 |
Determine the date of the last day of the specified month. |
||
|
97 |
Determine the difference between the start date and time and the end date and time. |
||
|
98 |
Determine a person's age on a reference date given their birth date. |
||
|
99 |
Round a date by the year, month, day, hour, or second. |
||
|
100 |
Truncate a date by the year, month, day, hour, or second. |
||
|
101 |
Determine the current date. |
||
|
102 |
Determine the current time. |
||
|
103 |
Determine the current data and time stamp. |
||
|
104 |
Perform operations on datetime data. |
||
|
105 |
|||
|
106 |
Null value |
Determine the first non-null value among the specified data. |
|
|
107 |
|||
|
108 |
|||
|
109 |
Data comparison |
Determine whether two data items are equal. |
|
|
110 |
Compare the values in the target data and the comparison data one at a time, and if there is a match, return the corresponding return value. |
||
|
111 |
Compare the values in the target data and in the comparison data one at a time, and, if any value in the target data is less than the value in the comparison data, return the corresponding return value. |
||
|
112 |
Determine the greatest value. |
||
|
113 |
Determine the smallest value. |
||
|
114 |
Data types |
Convert the data type. |
|
|
115 |
|||
|
116 |
Data information acquisition |
Determine the number of bytes in the target data. |
|
|
117 |
Determine the character code of the first character of character string data. |
||
|
118 |
Determine the character corresponding to numeric value character code in the target data. |
||
|
119 |
User information |
Determine the authorization identifier of the currently executing HADB user. |
![[Figure]](GRAPHICS/F0000206.GIF)
.- #
-
There are differences in specifications among the scalar functions RANDOM, RANDOMCURSOR, and RANDOMROW. For details about the differences in specifications, see (6) List of scalar functions that return pseudorandom numbers in 8.4.5 RANDOM.