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.
|
