HPlogo ALLBASE/SQL Release G3 Release Notes: HP 9000 Computer Systems > Chapter 2 What's in This Version?

Features
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

spacerspacerspacer
spacer
spacer
  		 
 

The following features are contained in G3 and later releases.

String Functions

With the G3 release of ALLBASE/SQL and IMAGE/SQL, the supported SQL syntax has been enhanced to include the following string manipulation functions: UPPER, LOWER, POSITION, INSTR, TRIM, LTRIM and RTRIM. These string functions allow you to manipulate or examine the CHAR and VARCHAR values within the SQL syntax, allowing for more sophisticated queries and data manipulation commands to be formed. These string functions were designed to be compatible with functions specified in the ANSI SQL '92 standard and functions used in ORACLE. In cases where the ANSI SQL '92 standard and the ORACLE functions were not compatible (such as the LTRIM and RTRIM in ORACLE versus TRIM in the ANSI standard), both versions were implemented. The specifications for each of these functions follows.

Function Specification

LOWER

Converts all the characters in stringexpr to lower case

Syntax

[LOWER (stringexpr)]

UPPER

Converts all the characters in stringexpr to upper case

Syntax

[UPPER (stringexpr)]

POSITION

Searches for the presence of the string stringexpr1 in the string stringexpr2 and returns a numeric value that indicates the position at which stringexpr1 is found in stringexpr2

Syntax

[POSITION (stringexpr1, stringexpr2)]

INSTR

Searches stringexpr1 beginning with its nth character for the mth occurrence of stringexpr2 and returns the position of the character in stringexpr1 that is the first character of this occurrence. If n is negative, Instr counts and searches backward from the end of stringexpr1. The value of m must be positive. The default values of both n and m are 1, meaning Instr begins searching at the first character of stringexpr1 for the first occurrence of stringexpr2. The return value is relative to the beginning of stringexpr1 regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if stringexpr2 does not appear m times after the nth character of stringexpr1) the return value is 0.

If n and m are not specified the function is equivalent to the ANSI SQL-92 POSITION function, except that the syntax is slightly different.

Syntax

[INSTR (stringexpr1, stringexpr2 [,n [,m]])]

LTRIM

LTRIM function trims the characters specified in charset from the beginning of the string stringexpr.

Syntax

[LTRIM (charset, stringexpr)]

RTRIM

RTRIM function trims the characters specified in charset from the end of the string stringexpr.

Syntax

[RTRIM (charset, stringexpr)]

TRIM

TRIM function allows you to strip the characters specified in charset from the beginning and/or the end of the string stringexpr. If charset is not specified, then blank characters would be stripped from stringexpr.

Syntax
[ TRIM ({ LEADING | TRAILING | BOTH} (,charset ,stringexpr)]

Examples:

Example 1 

 SELECT LOWER (OWNER) || '.' || LOWER (NAME)
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER ('vendors');

Returns "purchdb .vendors "

Example 2 

 SELECT POSITION ('world', 'hello world')
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER('vendors');

Returns the numeric value 7

Example 3 

 SELECT INSTR ('hello world hello world', 'world', 5, 2)
 FROM SYSTEM.TABLE
 WHERE NAME = UPPER('vendors');

Returns the numeric value 18 (starting position of the second occurrence of the string 'world')

Example 4 

 SELECT * FROM SYSTEM.TABLE
 WHERE NAME = LTRIM ('?*', 'VENDORS?*???***')
 AND OWNER = 'PURCHDB';

Returns the system table entry for PURCHDB.VENDORS

Example 5 

 SELECT TRIM (BOTH '?*' FROM '??**?*hello ?* world???*')
 FROM SYSTEM.TABLE
 WHERE NAME = 'VENDORS';

Returns 'hello ?* world'.

Security

Two new authorities have been added to the G2 release of ALLBASE/SQL: MONITOR and INSTALL. These new authorities can be granted to users using the GRANT command, or can be revoked from users using the REVOKE command.

MONITOR Authority

MONITOR authority gives a user the ability to run SQLMON, an on-line diagnostic tool that monitors the activity of an ALLBASE/SQL DBEnvironment. Before this new authority, SQLMON can only be run by the creator of the DBEnvironment (also known as the DBECreator), someone who knows the maintenance word (maint word), or someone who has superuser capability. See the ALLBASE/SQL Reference Manual for more details.

INSTALL Authority

INSTALL authority gives a user the ability to use the SQL preprocessors to create an SQL module owned by someone other than themselves, or to use ISQL to install a module owned by someone other than themselves. This is an important capability for those needing to move modules from a development DBEnvironment to a production DBEnvironment. Before this new authority, only a DBA had this ability. See the ALLBASE/SQL Reference Manual for more details.

Year 2000 Compatibility

As the end of the century quickly approaches there has been much concern about dates being handled correctly in the twenty-first century. This is particularly an issue for systems and applications that are using a two digit year to express dates. ALLBASE/SQL has always stored dates in a four digit year format which largely eliminates the year 2000 compatibility problem. ALLBASE/SQL applications can be coded using four digit year dates, thus avoiding any ambiguity. However, for existing applications that may allow dates to be entered using a two digit year format, there is still an issue as to how these dates get converted to the ALLBASE/SQL internal format using the TO_DATE function or TO_DATETIME. For example, what date value would the following functions return?

TO_DATE('12/31/35','MM/DD/YY')
TO_DATETIME('351231235959','YYMMDDHHMISS')

Would these functions evaluate the year as 1935 or 2035?

Solution

When YY is specified in the format specification of either TO_DATE or TO_DATETIME, and if its value of the year specified is less than 50, then the century part of the DATE or DATETIME defaults to 20, otherwise it is set to 19. This behavior can be overridden by setting the environment variable HPSQLsplitcentury to a value between 0 and 100. If the year specified is less than the value of the environment variable HPSQLsplitcentury then the century part is set to 20, otherwise it is set to 19.

For the above example,

Case 1: HPSQLsplitcentury is not set

TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59

Case 2: HPSQLsplitcentury is set to 0

TO_DATE('12/31/35','MM/DD/YY') = 1935-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 1935-12-31 23:59:59

Case 3: HPSQLsplitcentury is set to 70

TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59


Chapter 2 What's in This Version?
  		 


ODBCLink/SE  
Feedback to webmaster