How to convert Oracle case to case

In Oracle database, case conversion usually refers to converting the case of certain data or objects.

In Oracle database, the names and values ​​of some objects and data are case-sensitive, such as table and column names, variable names, function names, etc. This means that if you use uppercase names in one place, and call the object or data with lowercase names in another place, an error will be prompted.

For example, in the following SQL statements, errors will occur when company_id and COMPANY_ID are confused:

SELECT company_id FROM company WHERE COMPANY_ID=123;

At this time, case conversion is required to convert one name into the other. to avoid errors.

Oracle database provides multiple implementation methods for case conversion.

1. Use functions to achieve case conversion

You can use Oracle’s built-in functions to achieve case conversion. The three commonly used functions are:

1. UPPER : Convert all characters in the string to uppercase.
2. LOWER: Convert all characters in the string to lowercase.
3. INITCAP: Convert the first letter in the string to uppercase and the remaining characters to lowercase.

For example, convert the company_name in the table company from uppercase to lowercase:

UPDATE company SET company_name = LOWER(company_name);

2. Use quotation marks to distinguish case

Oracle database supports reference objects Use quoted names to convert names into case-sensitive form.

For example, use a quoted name when creating a table:

CREATE TABLE "MyTable" (
   "MyCol"      VARCHAR2(50),
   "MyDateCol"  DATE);

Note that when using a quoted name, the exact name within the quotes must be, regardless of case, otherwise it will will return an error.

3. Use parameters to set case conversion rules

Oracle database also provides a special parameter NLS_COMP and NLS_SORT, which can set case conversion rules.

The NLS_COMP parameter can set case sensitivity, such as enabling case insensitivity. You can use the following statement:

ALTER SESSION SET NLS_COMP = LINGUISTIC;

The NLS_SORT parameter can set sorting rules, such as enabling case-insensitive sorting. Rules, you can use the following statement:

ALTER SESSION SET NLS_SORT = BINARY_CI;

The above are the common case conversion methods in Oracle database. Using different conversion methods, you can choose the best conversion strategy according to the actual situation.

The above is the detailed content of How to convert Oracle case to case. For more information, please follow other related articles on the PHP Chinese website!