Sybase Cheat Sheet

A Programming › SQL cheat sheet in English by pxlcore

SQL Database Server Sap Sybase Sqlanywhere

17,022 views 3,573 downloads 4 Oct 2017 History

PRs

3.7 avg · 0 ratings

Sybase Data Types

TINYINT	Integer (0 to 255)
SMALLINT	Integer (-32768 to +32767)
INT	Integer (-2147483648 to +2147483647)
BIGINT	Integer (-9233372036854775808 to +9233372036854775807)
DECIMAL	Decimal (-10³⁸ and 10³⁸ -1)
NUMERIC	Decimal (-10³⁸ and 10³⁸ -1)
FLOAT	Decimal (8 byte, machine-dependent)
REAL	Decimal (4 byte, machine-dependent)
CHAR(n)	String (1 to 255)
NCHAR(n)	String (1 to 255)
VARCHAR(n)	String (1 to 255)
NVARCHAR(n)	String (1 to 255)
TEXT	String (2^31 characters)
MONEY	Decimal (922337203685477.5807 to -922337203685477.5808)
SMALLMONEY	Decimal (214748.3647 to -214748.3648)
DATE	Date (1753-01-01 to 9999-12-31)
DATETIME	DateTime (1753-01-01 to 9999-12-31)
SMALLDATETIME	DateTime (1900-01-01 to 2079-06-06)
TIME	Time (12:00:00AM to 11:59:59:999PM)
TIMESTAMP	Timestamp
BIGTIME	Time (12:00:00.000000 AM to 11:59:59.999999 PM)
BIGDATETIME	DateTime (0001-01-01 to 9999-12-31 and 12:00.000000AM to 11:59:59.999999 PM)
BIT	Boolean (0 or 1)
BINARY	Binary (0 - 256 bytes)
VARBINARY	Binary (0 - 255 bytes)
IMAGE	Blob (Binary 2³¹)

CASE Statement

CASE value-expression
  WHEN [constant | NULL] THEN statement-list …
  [ WHEN [constant | NULL] THEN statement-list ] …
  ELSE statement-list
END

IF Statement

IF condition THEN expression 
[ELSE expression] 
END [IF]

Local variables

DECLARE @c1 FLOAT, @c2 FLOAT
SELECT @c1 = 1000/1000
SELECT @c2 = @c1/30
SELECT @c1 , @c

LIST function [Aggregate]

LIST (
  [ALL | DISTINCT] string-expr 
  [, 'delimiter-string']
  [ORDER BY order-by-expr [ ASC | DESC ], ... ]
)

Example:
SELECT LIST(EmployeeID) FROM Employees GROUP BY DepartmentID

sa_split_list system procedure

Takes a string of values, separated by a delimiter, and returns a set of rows—one row for each value.
sa_split_list( str [, delim ] [, maxlen ])
Result set:
line_num Sequential number for the row.
row_value Value from the string, truncated to maxlen if required.

Local Temporary Table

DROP TABLE IF EXISTS table-name
DECLARE LOCAL TEMPORARY TABLE table-name
… ( col-name [ col-constraint ] …
[ , col-name [ col-constraint ] … ]
[ , table-constraint ] … )
…[ ON COMMIT { DELETE | PRESERVE } ROWS NOT TRANSACTIONAL]

Push data to temp table
INSERT INTO table-name SELECT ... or
SELECT * INTO table-name FROM ...

DatePart abbrevations

datepart	abbr	values
year	yy	1753–9999 (datetime) 1753–2079 (smalldatetime) 0001–9999 (bigdatetime)
quarter	qq	1-4
month	mm	1-12
week	wk	1-54
day	dd	1-31
dayofyear	dy	1-366
weekday	dw	1–7 (Sun. – Sat.)
hour	hh	0-23
minute	mi	0-59
second	ss	0-59
millisecond	ms	0-999
microseconds	us	0-999999

DateAdd

DATEADD(date-part, value, timestamp)

Example: add 102 months to the date
SELECT DATEADD(month, 102, '1987/05/02')

DateDiff

DATEDIFF(datepart, {date, date | time, time | bigtime, bigtime | datetime, datetime | bigdatetime, bigdatetime}])

Example: hours between two dates
SELECT DATEDIFF(hh, "apr 1, 1999", "apr 2, 1999")

REGEXP search condition

SELECT * FROM table-name
WHERE col-name REGEXP 'reg-expr';

Example:
SELECT Surname, Surname, City, Phone FROM Contacts WHERE Phone REGEXP '\\d{8}00';

REGEXP_SUBSTR function

SELECT REGEXP_SUBSTR( col-name, 'reg-expr' ) 
FROM table-name;

Example:
SELECT REGEXP_SUBSTR( Street, '(?<=^\\S+\\s+).*$' ) FROM Customers;

Badges

Nice Cheat Sheet Good Cheat Sheet Downloaded from Unusual Locations × 1

Comments

No comments yet.