Start of change

VERIFY_NAME scalar function

The VERIFY_NAME scalar function checks whether the input name is correctly constructed to be either a system object name or an SQL name.

The rules for system names can be found at this link: Names (*NAME)

The rules for SQL names can be found at this link: SQL identifiers

Authorization: None required.

Read syntax diagramSkip visual syntax diagramVERIFY_NAME( NAME => name,NAME_TYPE => name-type )

The schema is QSYS2.

name
A character string containing a name to be checked. If the name within the string is delimited with quote characters ("), the alphabetic characters will not be folded to uppercase. Lowercase alphabetic characters in a non-delimited name will be folded to uppercase.
name-type
A character string that specifies the type of name to be verified.
SQL
The name is checked for compliance with the SQL name rules. An SQL name can be up to 128 characters long.
SYSTEM
The name is checked for compliance with the system object name rules. A system name is limited to a length of 10.

The result of the function is Boolean. The function returns true if the name is allowed as name-type. The function returns false if the name is not correctly constructed as the specified name-type.

Example

Check a variety of names to see if they are valid.
Table 1. VERIFY_NAME results
Function invocation Result Notes
VALUES QSYS2.VERIFY_NAME('CHECKONE', 'SYSTEM'); true  
VALUES QSYS2.VERIFY_NAME('checktwo', 'SYSTEM'); true Name is folded to uppercase since it is not delimited
VALUES QSYS2.VERIFY_NAME('"checkthree"', 'SYSTEM'); false When a delimited system name contains lowercase letters, the length includes the delimiters
VALUES QSYS2.VERIFY_NAME('"checkthree"', 'SQL'); true  
VALUES QSYS2.VERIFY_NAME('A B', 'SYSTEM');
VALUES QSYS2.VERIFY_NAME('"A B"', 'SYSTEM');
 false A blank is never allowed in a system name
VALUES QSYS2.VERIFY_NAME('A B', 'SQL'); false A blank is not allowed in an undelimited SQL name
VALUES QSYS2.VERIFY_NAME('"A B"', 'SQL'); true A blank is allowed in a delimited SQL name
VALUES QSYS2.VERIFY_NAME('LONGERTHAN10', 'SYSTEM'); false Too long for a system name
VALUES QSYS2.VERIFY_NAME('LONGERTHAN10', 'SQL'); true  
VALUES QSYS2.VERIFY_NAME('TE.ST', 'SYSTEM'); true Period is allowed in a system name
VALUES QSYS2.VERIFY_NAME('TE.ST', 'SQL'); false Period is not allowed in an undelimited SQL name
VALUES QSYS2.VERIFY_NAME('"TE.ST"', 'SQL'); true  
End of change