Sybase Functions

Here I cover almost all of the important Sybase Functions.

The general syntax of the system functions is:
select function_name(argument[s])

System functions that return database information
col_length (object_name, column_name) Returns the defined length of column. Use datalength to see the actual data size.
datalength (expression) Returns the length of expression in bytes. expression is usually a column name. If
expression is a character constant, it must be enclosed in quotes.
db_name ([database_id ]) Returns the database name. database_id must be a numeric expression. If no
database_id is supplied, db_name returns the name of the current database.
host_name ( ) Returns the current host computer name of the client process process (not the Adaptive Server process).
isnull (expression1, expression2) Substitutes the value specified in expression2 when expression1 evaluates
to NULL.
next_identity (table_name) Retrieves the next identity value that is available for the next insert.
suser_name ([server_user_id]) Returns the server user’s name. Server user’s IDs are stored in syslogins. If no server_user_id is supplied, it returns the name of the current user.
user_name ([user_id]) Returns the user’s name, based on the user’s ID in the current database. If no user_id is supplied, it returns the name of the current user.

String functions

ascii (char_expr) Returns the ASCII code for the first character in the expression.
char (integer_expr) Converts a single-byte integer value to a character value. char is usually used as the inverse of ascii. integer_expr must be between 0 and 255. Returns a char datatype.

charindex (expression1, expression2) Searches expression2 for the first occurrence of expression1 and
returns an integer representing its starting position. If expression1 is not found, it returns 0. If expression1 contains wildcard characters, charindex treats them as literals.
difference (char_expr1, char_expr2) Returns an integer representing the difference between two soundex values. The difference function compares the soundex values of two strings and evaluates the similarity between them, returning a value from 0 to 4. A value of 4 is the best match.
lower (char_expr) Converts uppercase to lowercase. Returns a character value.
ltrim (char_expr) Removes leading blanks from the character expression. Only values equivalent to the space character in the SQL special character specification are removed.
patindex (“%pattern%”,char_expr [using {bytes |chars | characters}]) Returns an integer representing the starting position of the first occurrence of pattern in the specified character expression; returns 0 if pattern is not found.
reverse (expression) Returns the reverse of the character or binary expression; if expression is “abcd”, it returns “dcba”.
right (expression, integer_expr) Returns the part of the character or binary expression starting at the specified number of characters from the right. Return value has the same datatype as the character expression.
rtrim (char_expr) Removes trailing blanks. Only values equivalent to the space character in the SQL special character definition are removed.
soundex (char_expr) Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte Roman letters.
str (approx_numeric [, length [, decimal] ]) A string function, returns a character representation of the floating point number. select str(1234.7, 4)---- 1235,select str(-12345, 6)------ -12345,select str(123.45, 5, 2)----- 123.5
str_replace ("string_expression1","string_expression2","string_expression3") Replaces any instances of the second string expression (string_expression2) that occur within the first string expression (string_expression1) with a third expression (string_expression3).
stuff (char_expr1, start, length,char_expr2) Delete length characters from char_expr1 at start, and then insert char_expr2 into char_expr1 at start. To delete characters without inserting other characters, char_expr2 should be NULL, not ” ”, which indicates a single space.select stuff("abcdef", 2, 3, null)--- aef
substring (expression, start, length) Returns part of a character or binary string. start specifies the
character position at which the substring begins. length specifies the number of characters in the substring.
upper (char_expr) Converts lowercase to uppercase. Returns a character value.

Aggregate functions
avg, count,max, min,sum

Mathematical functions
abs (numeric) Returns the absolute value of a given expression
ceiling (numeric) Returns the smallest integer greater than or equal to the specified value.
exp (approx_numeric) Returns the exponential value of the specified value.
floor (numeric) Returns the largest integer that is less than or equal to the specified value.
pi () Returns the constant value of 3.1415926535897931
power (numeric, power) Returns the value of numeric to the power of power.
round (numeric, integer) Rounds the numeric so that it has integer significant digits. A positive integer determines the number of significant digits to the right of the decimal point; a negative integer, the number of significant digits to the left of the decimal point.

Date functions

datename (datepart, date) Part of a datetime, smalldatetime, date or time value as an ASCII string. select datename (mm, "1997/06/16")-----------June
datediff (datepart, date, date) The amount of time between the second and first of two dates, converted to the specified date part (for example, months, days, hours). select datediff (yy, "1984", "1997")----------13
dateadd (datepart, number,date) A date produced by adding date parts to another date. select dateadd (dd, 16, "1997/06/16")------------------------------Jul 2 1997 12:00AM
getdate () Returns current system date and time.
getutcdate () Returns a datetime whose value is in Coordinated Universal Tiime (sometimes called Greenwich Mean Time).

User-defined SQL functions

Adaptive Server does not allow fetch statements in a scalar function that return data to the client. You cannot include :
• select or fetch statements that returns data to the client.
• insert, update, or delete statements.
• Utility commands, such as dbcc, dump and load commands.
• print statements
• Statements that references rand, rand2, getdate, or newid.
You can include select or fetch statements that assign values only to local variables.

User Defined Sql Function Example 

create function BONUS(@salary int, @grade int, @dept_id int)
returns int
as
    begin
    declare @bonus int
    declare @cat int
    set @bonus = 0
    select  @cat = dept_cat from department 
        where dept_id = @dept_id

    if (@cat < 10)
        begin
            set @bonus = @salary *15/100

        end
        else
            begin
            set @bonus = @salary * 10/100
        end
return @bonus
end