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 (-1038 and 1038 -1) |
NUMERIC |
Decimal (-1038 and 1038 -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 231) |
|
|
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;
|
Created By
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets