AntDB-Oracle Compatibility Developer’s Manual P3–38
Published: 2023-09-08 10:20:50

#### String functions
The functions used to detect and manipulate string values are described in this section. AntDB supports implicit type conversion, which will convert function parameters to reasonable parameter types, but not all type conversions are supported. So when users use functions, it is better to follow the parameter type requirements of each function.

##### String functions that return character types

| Name| Number of arguments| Function| For example:
|----------|----------|----------|----------
| CHR| 1| Convert ASCII to characters| CHR(56)  CHR (196 USING NCHAR\_CS)
| CONCAT| 2| CONCAT(char1, char2) string concatenation| concat('Ant', 'DB')
| INITCAP| 1| Return the first letter of each word in uppercase and the other letters in lowercase| INITCAP('the soap')
| LOWER| 1| String conversion to lowercase| LOWER('MR. SCOTT MCMILLAN')
| UPPER| 1| String conversion to uppercase| UPPER(last\_name)
| LPAD| 2~3| LPAD(string, length INTEGER \[, fill ]) fills the string from the left with the characters specified by fill (the default is spaces) to reach length. If the string is longer than the length specified by length, the string is truncated from the right.| LPAD('Page 1',15,'\*.')
| RPAD| 2~3| RPAD(string, length INTEGER \[, fill ]) fills the string string from the right side to the length specified by length by using the character fill (default is space). If the length of string exceeds the value of length, then string is truncated to the length specified by length| 
| LTRIM| 1~2| Removes spaces or characters from the left side of a string| LTRIM('\<=====>BROWNING\<=>', '\<>=')====
| RTRIM| 1~2| Removes spaces or characters from the right side of a string| RTRIM('\<=====>BROWNING\<=>', '\<>=')====
| NLS\_INITCAP| 1| NLS\_INITCAP (string) function: return string with the first letter of each word capitalized and the other letters in the word lowercase. This function supports 2 parameters in Oracle, AntDB only partially supports it.| select nls\_initcap('a c b d e') from dual;
| NLS\_LOWER| 1| NLS\_LOWER (string) returns string with all letters in lowercase form. This function supports 2 parameters in Oracle, AntDB only partially supports it.| select nls\_lower('A c B d e') from dual;
| NLS\_UPPER| 1| nls\_upper (string) returns string with all letters in the string in uppercase form. This function supports 2 parameters in Oracle, AntDB only partially supports it.| select nls\_upper('A c B d e') from dual;
| REGEXP\_REPLACE| 2~6| REGEXP\_REPLACE ( source\_char, pattern \[, replace\_string \[, position \[, occurrence \[, match\_param ]]] ] ) is used to match replacement by regular expressions, by default, every time a regular is matched, it is replaced with replace\_string| SELECT regexp\_replace('13,453,5', '(\[0-9]{2,3})', '''\\1''') FROM dual;
| REGEXP\_SUBSTR| 2~6| REGEXP\_SUBSTR(String, pattern, position, occurrence, modifier) searches for a string that matches the specified pattern in source\_char.| REGEXP\_SUBSTR('11a22A33a','\[\^A]+',1,1,'i')
| REGEXP\_COUNT| 2~4| REGEXP\_COUNT ( source\_char, pattern \[, position \[, match\_param]]) The number of times pattern appears in the source\_char string. If no match is found, the function returns 0| REGEXP\_COUNT('reinitializing', 'i', 1)
| REPLACE| 2~3| REPLACE(char, search\_string \[, replacement\_string ]) string replacement| select replace ('asd','sd','aa') from dual;
| TRANSLATE| 3| TRANSLATE ( 'char' , 'from\_string' , 'to\_string' ) returns the string after replacing each character in the from\_string with the corresponding character in the to\_string.| TRANSLATE('SQL\*Plus User''s Guide', ' \*/''', '\_\_\_')
| SOUNDEX| 1| Returns the sound representation of string. This is useful for comparing two words with different spellings but similar pronunciations| SOUNDEX(last\_name) = SOUNDEX('SMYTHE')
| SUBSTR| 2~3| SUBSTR (char, position \[, substring\_length ]) fetches the number of characters specified by substring\_length from the string at the position specified by position, and fetches it as a substring from string. If count is not specified, then the length of the substring is from the beginning of the string to the end of the string.| select substr('Hello World', 1,3) from dual;

##### String functions that return value types

| Name| Number of arguments| Function| For example:
|----------|----------|----------|----------
| ASCII| 1| The number of ASCII codes corresponding to the character| ASCII(SUBSTR(last\_name, 1, 1))
| INSTR| 2~4| INSTR(string , substring \[, position \[, occurrence ] ]) The position of the string to be intercepted in the source string| INSTR('CORPORATE FLOOR','OR', 3, 2)
| LENGTHB| 1| LENGTHB gives the byte of the string| select lengthb('hello') from dual;
| LENGTH| 1| length(string)string returns the number of characters in the string| select length('hello') from dual;
| REGEXP\_COUNT| 2~4| REGEXP\_COUNT ( source\_char, pattern \[, position \[, match\_param]])  returns the number of times pattern appears in the source\_char string. If no match is found, the function returns 0. The position variable tells Oracle at what position in the source string to start the search. Each occurrence of the pattern after the start position increases the count by 1.| select REGEXP\_COUNT('GEORGE','GE',1,'i') from DUAL;
| REGEXP\_INSTR| 2~7| Regular expressions search for strings and return information about the position of the matching content in the string.| REGEXP\_INSTR('500 Oracle Parkway, Redwood Shores, CA', '\[\^ ]+', 1, 6)

#### Date and Time Function

The following table shows all the functions that can be used to handle date/time type values. Before proceeding with this section, you should refer to the background information on date/time types in the previous data types chapter.

| Name| Number of arguments| Function| For example:
|----------|----------|----------|----------
| ADD\_MONTHS| 2| ADD\_MONTHS(DATE, NUMBER) returns the date specified by the parameter DATE plus the number of months specified by the parameter NUMBER.| ADD\_MONTHS(hire\_date, 1)
| EXTRACT (datetime)| 2| EXTRACT ( { YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND } FROM { expr }) finds the field value for the date or interval value| SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL;
| LAST\_DAY| 1| LAST\_DAY(DATE) returns the date representing the last day of the month. If the date contains a time component, this content has no effect on the result.| LAST\_DAY(SYSDATE)
| MONTHS\_BETWEEN| 2| MONTHS\_BETWEEN(date1, date2) returns the number of months between two day periods| select months\_between(to\_date('2018-11-12', 'yyyy-mm-dd'), to\_date('2017-11-12', 'yyyy-mm-dd')) as zs from dual;
| NEW\_TIME| 3| NEW\_TIME(date, timezone1, timezone2) converts a date and time value to a date and time value in another time zone| new\_time(sysdate,'PDT','GMT')
| NEXT\_DAY| 2| NEXT\_DAY(DATE, dayofweek) parameter 2 is an enumeration value that specifies the date after the date represented by the parameter dayofweek.| SELECT NEXT\_DAY(SYSDATE, 2) FROM DUAL;
| ROUND (date)| 1~2| ROUND (r\[,f]) rounds the date r to the format of f. If f is not filled, it is rounded to the nearest day. The ROUND (date) parameter 2 is an enumeration value. CC,SCC SYYYY,YYYY,YEAR,SYEAR,YYY,YY,Y IYYY,IY,IY,I Q MONTH,MON,MM,RM WW IW W DDD,DD,J,Day DAY,DY,D HH,HH12,HH24,Hour MI| ROUND (TO\_DATE ('27-OCT-00'),'YEAR')
| SYS\_EXTRACT\_UTC| 1| SYS\_EXTRACT\_UTC(datetime\_with\_timezone) returns the standard UTC time that corresponds to the specified time| SELECT SYS\_EXTRACT\_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00') FROM DUAL;
| TRUNC (date)| 1~2| TRUNC（date\[,fmt]） intercepts the date in the specified format| TRUNC(TO\_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
| CURRENT\_DATE| 0| Called without parentheses and parameters, returns the current date of the system| CURRENT\_DATE
| CURRENT\_TIMESTAMP| 0~1| CURRENT\_TIMESTAMP \[ (timestamp\_precision) ] can be used with or without parameters to return the current date in the current session time zone as timestamp with time zone data type| CURRENT\_TIMESTAMP
| DBTIMEZONE| 0| Called without parentheses and parameters| DBTIMEZONE
| SYSDATE| -1| Return the time of the current system| SYSDATE
| SYSTIMESTAMP| -1| Returns the current system date and time (including microseconds and time zone) on the local database| SYSTIMESTAMP
| cSESSIONTIMEZONE| -1| Return to session time zone| cSESSIONTIMEZONE
| LOCALTIMESTAMP| 0~1| Can be used with or without parameters| LOCALTIMESTAMP \[ (timestamp\_precision) ]