Table A-3. Sybase Adaptive Server-supported functions
Sybase function
|
SQL Server function
|
Notes
|
---|
To retrieve the application name on Sybase, use the following SQL statement: select program_name from master..sysprocesses where spid = @@spid
|
APP_NAME( )
|
Returns application name for current session; set by application.
|
N/A
|
BINARY_CHECKSUM({* | expression [,...]})
|
Returns binary checksum for list of expressions or row of a table.
|
N/A
|
CAST(expression AS datatype)
|
Converts a valid SQL Server expression to the specified datatype. On Sybase Adaptive Server Enterprise, use the CONVERT function instead.
|
CHAR_LENGTH(expression)
|
N/A
|
Returns the number of characters in the expression. On SQL Server, the LEN function provides equivalent functionality.
|
N/A
|
CHECKSUM({* | expression [,...]})
|
Returns checksum value (computed over row values or expressions provided).
|
N/A
|
CHECKSUM_AGG([ALL | DISTINCT] expression)
|
Returns checksum of the values in group.
|
N/A
|
COALESCE(expression [,...])
|
Returns the first non-NULL argument from a list of arguments.
|
COMPARE(char_expression1, char_expression2 [, {collation_name | collation_ID}])
|
N/A
|
Returns the following values, based on the collation rules: 1 : char_expression1 is greater than char_expression2 0: char_expression1 is equal to char_expression2 -1 : char_expression1 is less than char_expression2
|
N/A
|
CONTAINS({column |* }, 'contains_search_condition'})
|
Searches columns on exact or "fuzzy" matches of contains_seach_condition. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information.
|
N/A
|
CONTAINSTABLE(table, column, contains_search_condition)
|
Returns a table with exact and "fuzzy" matches of contains_search_condition. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information.
|
N/A
|
COUNT_BIG([ALL | DISTINCT] expression)
|
Same as COUNT, except returns big integer.
|
N/A
|
DATABASEPROPERTYEX(database, property)
|
Returns database option or property.
|
N/A
|
DAY(date)
|
Returns an integer value representing the day of the date provided as a parameter.
|
N/A
|
FILE_ID('file_name')
|
Returns the file ID for the logical filename.
|
N/A
|
FILE_NAME(file_id)
|
Returns the logical filename for file ID.
|
N/A
|
FILEGROUP_ID('filegroup_name')
|
Returns filegroup ID for the logical filegroup name.
|
N/A
|
FILEGROUP_NAME(filegroup_id)
|
Returns the logical filegroup name for filegroup ID.
|
N/A
|
FILEGROUPPROPERTY(filegroup_name, property)
|
Returns filegroup property value for the specified property.
|
N/A
|
FILEPROPERTY(file, property)
|
Returns file property value for the specified property.
|
N/A
|
FULLTEXTCATALOGPROPERTY(catalog_name, property)
|
Returns full-text catalog properties.
|
N/A
|
FULLTEXTSERVICEPROPERTY(property)
|
Returns full-text service-level properties.
|
N/A
|
FORMATMESSAGE(msg_number, param_value [,... ])
|
Constructs a message from an existing message in SYSMESSAGES table (similar to RAISERROR).
|
N/A
|
FREETEXTTABLE(table { column |*}, 'freetext_string' [, top_n_by_rank])
|
Used for full-text search; returns a table with columns that match the meaning but don't exactly match value of freetext_string.
|
N/A
|
GETANSINULL(['database'])
|
Returns default nullability setting for new columns.
|
N/A
|
GETUTCDATE( )
|
Returns Universal Time Coordinate (UTC) date.
|
N/A
|
GROUPING(column_name)
|
Returns 1 when the row is added by CUBE or ROLLUP; otherwise, returns 0.
|
HEXTOINT(hexadecimal_string)
|
N/A
|
Returns an integer equivalent to the hexadecimal argument. On SQL Server, the function is CONVERT(INT, hexadecimal_value). However, the function does not work with hexadecimal strings as it does with Sybase, but with true hexadecimal values.
|
N/A
|
IDENT_INCR('table_or_view')
|
Returns identity-column increment value.
|
N/A
|
IDENT_SEED('table_or_view')
|
Returns identity seed value.
|
N/A
|
IDENT_CURRENT('table_name')
|
Returns the last identity value generated for the specified table.
|
N/A
|
IDENTITY(data_type [, seed, increment]) As column_name
|
Used in SELECT INTO statement to insert an identity column into the destination table.
|
N/A
|
INDEXPROPERTY(table_id, index, property)
|
Returns index property (such as Fillfactor).
|
INTTOHEX(integer_expression)
|
N/A
|
Returns the hexadecimal string equivalent to the integer argument. For equivalent functionality on SQL Server, use CONVERT(VARBINARY(8), integer_expression).
|
N/A
|
ISDATE(expression)
|
Validates if a character string can be converted to DATETIME.
|
N/A
|
IS_MEMBER({'group' | 'role'})
|
Returns true or false (1 or 0) depending on whether user is a member of NT group or SQL Server role. For equivalent functionality on Sybase, use PROC_ROLE function.
|
N/A
|
IS_SRVROLEMEMBER('role' [,'login'])
|
Returns true or false (1 or 0), depending on whether user is a member of specified server role.
|
N/A
|
ISNUMERIC(expression)
|
Validates if a character string can be converted to NUMERIC. For equivalent functionality on Sybase, use the following statement:
SELECT 1 WHERE expression NOT LIKE '%[^0-9]%'
|
IS_SEC_SERVICE_ON(`security_service')
|
N/A
|
Returns 1 if the security service is active; returns 0 otherwise.
|
lct_admin({{'LASTCHANCE' | `LOGFULL'}
|
N/A
|
Modifies the last-chance threshold for databases or processes; useful for terminating deadlocked processes.
|
N/A
|
LEFT(character_expression, integer_expression)
|
Returns a portion of a character expression, starting at integer_expression from left.
|
N/A
|
LEN(string_expression)
|
Returns the number of characters in the expression. On Sybase, the CHAR_LENGTH function provides equivalent functionality.
|
LICENSE_ENABLED('feature')
|
N/A
|
Returns 1 if a feature's license is active.
|
N/A.
|
MONTH(date)
|
Returns month part of the date provided.
|
MUT_EXCL_ROLES(role1, role2 [, {membership | activation }] )
|
N/A
|
Returns information regarding the mutual exclusivity between role1 and role2.
|
N/A
|
NCHAR(integer_expression)
|
Returns the Unicode character with the given integer code. On Sybase, use the TO_UNICHAR function.
|
N/A
|
NEWID( )
|
Creates a new unique identifier of type UNIQUEIDENTIFIER.
|
N/A
|
NULLIF(expression, expression)
|
Returns NULL if two specified expressions are equivalent.
|
N/A
|
OBJECTPROPERTY(id, property)
|
Returns properties of objects in the current database.
|
N/A
|
OPEN{[Global]cursor_name | cursor_variable_name}
|
Opens local or global cursor.
|
N/A
|
OPENDATASOURCE(provider_name, init_string)
|
Makes a connection to data source without using a linked server name.
|
N/A
|
OPENQUERY(linked_server, 'query')
|
Queries a remote data source previously setup as a linked server.
|
N/A
|
OPENROWSET('provider_name', {'datasource'; 'user_id',password | 'provider_string'},{[catalog.][schema.]object | 'query'})
|
Queries a remote data source without setting it up as a linked server.
|
N/A
|
PARSENAME('object_name', object_piece)
|
Returns database name, owner name, server name, or object name for the object specified. Object_piece is an integer between 1 and 4.
|
PATINDEX('%pattern%', expression)
|
N/A
|
Returns position of the first occurrence of a pattern in a string.
|
N/A
|
PERMISSIONS(object_id, column)
|
Returns a value containing a bitmap with current user's permissions on the specified object/column.
|
PROC_ROLE('role_name')
|
N/A
|
Returns 0 if the current user has not been granted the role specified by role_name. On SQL Server, the IS_MEMBER function offers equivalent functionality.
|
N/A
|
REPLACE('string_expression1','string_expression2','string_expression3)
|
Replaces the occurrence of the second expression with the third expression IN the first expression. Same as STUFF.
|
ROLE_CONTAIN('role1',' role2')
|
N/A
|
Returns 1 if role1 is contained within role2.
|
ROLE_ID(role_name)
|
N/A
|
Returns the role identifier for the role with name role_name. On SQL Server, use the following statement:
SELECT uid FROM sysusers WHERE issqlrole <> 0 AND name = role_name This statement will return the role identifier for the role with name matching role_name.
|
ROLE_NAME(role_id)
|
N/A
|
Returns the role name matching the role identifier equal to role_id. On SQL Server, use the following statement:
SELECT name FROM sysusers WHERE issqlrole <> 0 AND uid = role_id
|
ROWCNT(sysindexes.doampg)
|
N/A
|
Provides an estimate of the number of rows in a table. For equivalent functionality on SQL Server, use the following statement:
SELECT rowcnt FROM sysindexes WHERE name = 'table_name'
|
N/A
|
ROWCOUNT_BIG( )
|
Returns the number of rows affected by last query (same as @@ROWCOUNT).
|
N/A
|
SESSION_USER
|
Returns username in the current connection.
|
SHOW_ROLE( )
|
N/A
|
Returns a list of roles for the current user.
|
SHOW_SEC_SERVICES( )
|
N/A
|
Returns a list of security services currently in use.
|
SORTKEY(char_expr [, {collation_name | collation_id}])
|
N/A
|
Returns a value that can be used to order the values in char_expr by the specified collation.
|
N/A
|
STATS_DATE(table_id, index_id)
|
Returns date and time when index statistics were last updated.
|
N/A
|
STDEV(expression)
|
Returns standard deviation of values in the column.
|
N/A
|
STDEVP(expression)
|
Returns standard deviation for the population of values.
|
SUSER_ID(['login'])
|
N/A
|
Returns the user identifier for the current user or specified login. The SQL Server equivalent to this function is SUSER_SID.
|
N/A
|
SUSER_SID(['login'])
|
Returns Security ID (SID) for the current user or specified login in binary format. The Sybase equivalent to this function is SUSER_ID.
|
SUSER_NAME([server_user_id])
|
N/A
|
Returns the login name for the current user or specified login's system identifier. The SQL Server equivalent is SUSER_SNAME.
|
N/A
|
SUSER_SNAME([server_user_sid])
|
Returns login name for the current user or specified login's Security ID (SID). The Sybase equivalent is SUSER_NAME.
|
SYB_SENDMSG(ip_address, port, message)
|
N/A
|
Sends a UDP data packet containing the message argument to the ip_address.
|
N/A
|
SYSTEM_USER
|
Returns login name for the current session.
|
TO_UNICHAR(integer)
|
N/A
|
Returns a single character Unicode expression equivalent to the integer argument. On SQL Server use the NCHAR function.
|
TSEQUEL(timestamp_expr, timestamp_literal)
|
N/A
|
Returns true if timestamp_expr and timestamp_literal have matching values. SQL Server has equivalent functionality when using the equivalency operator (=) between the two values.
|
N/A
|
TYPEPROPERTY(datatype,property)
|
Returns information about datatype properties.
|
UHIGHSURR(uchar_expr, start)
|
N/A
|
Returns 1 if the Unicode value in uchar_expr at position start is the upper half of a surrogate pair.
|
ULOWSURR(uchar_expr, start)
|
N/A
|
Returns 1 if the Unicode value in uchar_expr at position start is the lower half of a surrogate pair.
|
N/A
|
UNICODE(`ncharacter_expression')
|
Returns the Unicode integer value for the first character of the input parameter. The Sybase equivalent of this function is USCALAR.
|
USCALAR(uchar_expr)
|
N/A
|
Returns the scalar value for the first Unicode character in uchar_expr. The SQL Server equivalent is the UNICODE function.
|
VALID_NAME(user_name)
|
N/A
|
Returns the value 0 if user_name is not a valid username for any database on the server.
|
VALID_USER(user_id)
|
N/A
|
Returns the value 1 if user_id is a valid user identifier for any database on the server.
|
N/A
|
VAR(expression)
|
Returns statistical variance in a column.
|
N/A
|
VARP(expression)
|
Returns statistical variance for a population for all values in the expression.
|
N/A
|
YEAR(date)
|
Returns an integer which is a YEAR part of the specified date.
|