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

DML Commands

The main DML commands are:
select, insert, update, delete

• select – Retrieves information from the database. "select into" is used to perform create table and insert functionality.
•  insert – adds new rows to a table.
• update – changes existing rows in a table.
• delete – removes specific rows from a table.

Select Command Syntax
SELECT tname
FROM SYS.SYSCATALOG
WHERE tname LIKE 'SYS%' ;

SELECT count(*)
FROM Employees;

SELECT year, model, color, sum(sales)
FROM sales_tab
GROUP BY ROLLUP (year, model, color);

SELECT * INTO #TableTemp FROM lineitem
WHERE l_discount < 0.5

------------------------------------------------------------------------------------------------------
Insert Command Syntax
insert into table_name
values (constant1, constant2, ...)
Use a separate insert statement for each row you add. 
 
insert into table_name
select column_list
from table_list
where search_conditions

Insert Example
insert into publishers (pub_id, pub_name)
values ("1756", "The Health Center")
------------------------------------------------------------------------------------------------------ 
Update Command Syntax
update titles
set price = price * 2

update titles
set total_sales = total_sales + 1, @price = price
where title_id = "BU1032"

update authors
set state = "PC", city = "College Town"
where state = "CA" and city = "Oakland"

update titleauthor
set title_id = titles.title_id
from titleauthor, titles, authors
where titles.title = "The Psychology of Computer Cooking"
and authors.au_id = titleauthor.au_id
and au_lname = "Stringer"

As an alternative to the from clause in the update statement, you can use a subquery, which is ANSI-compliant.

update stores_cal
set stor_name = "Barney’s"
where syb_identity = 1
You can use the syb_identity keyword, qualified by the table name, where necessary, to update an IDENTITY column.
------------------------------------------------------------------------------------------------------ 
Delete Command Syntax
delete titles
from authors, titles, titleauthor
where titles.title_id = titleauthor.title_id
and authors.au_id = titleauthor.au_id
and city = "Big Bad Bay City"

delete sales_monthly
where syb_identity = 1
------------------------------------------------------------------------------------------------------ 

Sybase Language Breakup

The main commands in Sybase can be broadly classified into three groups. They are:-

Data Definition Language

create, drop, alter, Referential integrity statements

Data Manipulation Language

select, insert, update, delete

Data Control Language

grant, revoke

Other important DDL command is Truncate

Where DELETE statement deletes rows from table matching specifications in WHERE clause .
TRUNCATE deallocates memory page(s) allocated for the specific table in memory and hence it is a DDL statement rather than DML. Of-course DELETE can be executed with out using WHERE calause to delete all the rows , however the page(s) are not deallocated .Additional info : DELETE statement is logged operation and hence can be rolled back where as TRUNCATE is not and hence can not be rolled back

Sybase Basics

Compiled objects 

A compiled object is any object that requires entries in the sysprocedures table, these include Check constraints,Defaults,Rules,Stored Procedures,Triggers,Views, Functions

String concatenation operator
The string operator + can concatenate two or more character or binary expressions. For example:
select Name = (au_lname + ", " + au_fname)
from authors

Quotation marks
There are two ways to specify literal quotes within a char or varchar entry.
The first method is to use an additional quote with a quote of the same type.

• 'I don''t understand.'

The second method is to enclose a quote in the opposite kind of quotation mark. 

• 'George said, "There must be a better way."'
• "Isn't there a better way?"

A stored procedure is a collection of SQL statements and optional control-of-flow statements stored under a name.

A trigger is a stored procedure that instructs the system to take one or more actions when a specific change is attempted. Triggers can nest to a depth of 16 levels.

A default is a value linked to a particular column or datatype, and inserted by the system if no value is provided during data entry.

Rules are user-defined integrity constraints linked to a particular column or datatype, and enforced at data entry time.

parseonly, noexec, prefetch, showplan, rowcount, nocount, and tablecount control the way a query is executed. The statistics options display performance statistics after each query. flushmessage determines when Adaptive Server returns messages to the user.

Comments
Two types:

--comment

 /* Multiline
    Comment*/
   
isql utility
You can use Transact-SQL directly from the operating system with the standalone utility program isql.
isql -S server_name -D database_name -U user_name -P password
1>
You can now start issuing Transact-SQL commands.

Default databases
When your Adaptive Server account was created, you may have been assigned a default database to which you are connected when you log in. If you were not assigned a default database, you are connected to the master database. To change your default database, use sp_modifylogin.
Example 1
Changes the default database for “sarah” to pubs2:
sp_modifylogin sarah, defdb, "pubs2"