NLS Functions

Oracle provides a subset of string functions oriented toward work with the national language character set. These functions (summarized in Table 8-4) handle cases where the underlying character set matters to the results. For example, if you were using an ASCII character set as your database character set and an EBCDIC character set as your national language character set, you would find that the two character sets had different letters associated with the code 65. You can use CHR to translate 65 to the associated character in the database character set, and the NLS function NCHR to translate 65 to a character in the national language character set.

Table 8-4. National language support functions
Name Description
NCHR Returns a value from the national language character set.
NLS_INITCAP Uppercases the first letter of each word (like INITCAP) and allows you to specify a linguistic sorting sequence.
NLS_LOWER Lowercases a string in accordance with language-specific rules.
NLS_UPPER Uppercases a string in accordance with language-specific rules.
NLSSORT Returns a string of bytes that can be used to sort a string in accordance with language-specific rules.
TO_NCHAR Converts database character set data into its national character set equivalent.

 

NCHR  
   

NCHR functions similarly to CHR, but returns a value from the national language character set. The specification is:

FUNCTION NCHR(code_location NUMBER) RETURN NVARCHAR2;

Values returned by NCHR should generally be assigned to NVARCHAR2 or NCHAR2 variables. To do otherwise is to force a character set conversion that you probably don't need. Following is an example of using NCHR:

DECLARE x NVARCHAR2(30);BEGIN x := NCHR(65); ...

The CHR function's USING NCHAR_CS clause provides the same functionality as NCHR.

NLS_INITCAP  
   

The NLS_INITCAP function uppercases the first letter of each word in a string, just as the INITCAP function does. NLS_INITCAP also allows you to specify a linguistic sorting sequence that affects the definition of "first letter." The syntax for using NLS_INITCAP is:

NLS_INITCAP(string1, [, 'NLS_SORT=sort_sequence_name'])

The following example illustrates the difference between INITCAP and NLS_INITCAP:

BEGIN DBMS_OUTPUT.PUT_LINE(INITCAP('ijzer')); DBMS_OUTPUT.PUT_LINE(NLS_INITCAP('ijzer','NLS_SORT=XDUTCH'));END;

The output is:

IjzerIJzer

In the Dutch language, the character sequence "ij" is treated as a single character. NLS_INITCAP correctly recognizes this as a result of the NLS_SORT specification, and uppercases the word "ijzer" (Dutch for "iron") appropriately.

NLS_INITCAP returns a VARCHAR2 value. The inputs can be from any character set.

NLS_LOWER  
   

The NLS_LOWER function lowercases a string in accordance with language-specific rules. The syntax for calling NLS_LOWER is:

NLS_LOWER(string1, [, 'NLS_SORT=sort_sequence_name'])

See NLS_INITCAP for a description of how the NLS_SORT specification can affect the results.

NLS_UPPER  
   

The NLS_UPPER function uppercases a string in accordance with language-specific rules. The syntax and usage are the same as for NLS_LOWER.

NLSSORT  
   

The NLSSORT function returns a string of bytes that can be used to sort a string value in accordance with language-specific rules. The string returned is of the RAW datatype. The syntax for invoking NLSSORT is as follows:

NLSSORT(string1, [, 'NLS_SORT=sort_sequence_name'])

Following is an example of how NLSSORT can be used to ensure that two string values are compared according to language rules:

IF NLSSORT(x, 'NLS_SORT=XFRENCH') > NLSSORT(y, 'NLS_SORT=XFRENCH') THEN...

In this case, the IF statement is testing to see whether the string x is "greater" than the string y. If a simple x > y comparison were used, the results would depend simply on whether the binary representation of x was greater than y. For some languages, the underlying binary representation does not correlate to the sort sequence. The RAW strings returned by NLSSORT allow you to compare two values in the appropriate way for the ordering of characters in the language being used.

TO_NCHAR  
   

TO_NCHAR is an overloaded function that can be used to convert either numbers, dates, or character strings to character strings in the national character set. The two TO_NCHAR functions (one for numbers, one for dates) work just like the TO_CHAR function described in Chapter 9. The one difference is that the return value is an NVARCHAR2 rather than a VARCHAR2.

This version of TO_NCHAR converts database character set data into its national character set equivalent. TO_NCHAR is the opposite of TO_CHAR, which is described in the previous section. The specification of TO_NCHAR is as follows:

FUNCTION TO_NCHAR(char_data IN VARCHAR2) RETURN NVARCHAR2

TO_NCHAR can accept any of the following types as input: CHAR, VARCHAR2, CLOB, and NCLOB. The return type is always NVARCHAR2.

Following is an example of TO_NCHAR's translating a string from the database character set into the national character set:

DECLARE a VARCHAR2(30) := 'Corner? What corner?'; b NVARCHAR2(30);BEGIN b := TO_NCHAR(a);END;

If you're planning to use TO_NCHAR, you should also review the use of TRANSLATE...USING, described earlier. Also be aware that TO_NCHAR may be used in the same manner as TO_CHAR to convert dates, times, and numbers into human-readable form. Such uses are described in Chapter 9 and Chapter 10.

Chapter 9. Numbers

Where would we be without numbers? While those of us who are math-challenged might prefer a text-only view of the world, the reality is that much of the data in any database is numeric. How much inventory do we have? How much money do we owe? At what rate is our business growing? These are just some of the questions that we expect to answer using numbers from databases.

When working with numbers in PL/SQL, you need to have at least a passing familiarity with the following:

· The numeric datatypes at your disposal. It also helps to know in what situations they are best used.

· Conversion between numbers and their textual representations. How else do you expect to get those numbers into and out of your database?

· PL/SQL's rich library of built-in numeric functions. After all, you don't want to reinvent the wheel.

Each of these topics is discussed in this chapter. We'll begin by looking at the datatypes themselves.

9.1 Numeric Datatypes

PL/SQL, just like the Oracle RDBMS, offers a variety of numeric datatypes to suit different purposes. There are basically three numeric datatypes you need to know about:

· NUMBER

· PLS_INTEGER

· BINARY_INTEGER

In practice, you may encounter other numeric types, such as FLOAT and DECIMAL. These are really nothing more than alternate names for the three core numeric types just listed. We'll talk about these alternate names later, in Section 9.1.4.

Oracle's numeric types are designed to perform identically across all platforms that Oracle supports, enhancing portability. For example, a NUMBER division performed on Intel hardware will be rounded in the same manner and yield the same results as a NUMBER division performed on Sun hardware. Oracle achieves this portability by implementing its own math routines rather than using the underlying hardware-based math functions. An exception is the PLS_INTEGER type, which trades hardware-independence for execution speed.



/footer.php"; ?>