This is an old revision of the document!
SQLines open source tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Sybase Adaptive Server Enterprise (Sybase ASE) to MariaDB.
Databases:
Converting SQL language elements:
Sybase ASE | MariaDB | |||
1 | string + string2 + ... | String concatenation, NULL is treated as '' | CONCAT(string, string2, ...) | Result is NULL if any string is NULL |
Converting data types:
Sybase ASE | MariaDB | |||||
1 | BIGINT | 64-bit integer | BIGINT | |||
2 | DATE | Date (year, month and day) | DATE | |||
3 | DATETIME | Date and time with 1/300 seconds fraction | DATETIME(3) | |||
4 | SMALLDATETIME | Date and time with accuracy to minute | DATETIME |
String functions:
Sybase ASE | MariaDB | ||
1 | ASCII(string) | Get ASCII value of left-most character | ASCII(string) |
2 | CHAR(code) | Get character from its ASCII code | CHAR(code) |
3 | CHAR_LENGTH(string) | String length in characters | CHAR_LENGTH(string) |
4 | CHARINDEX(substr, string [,pos]) | Get position of substr in string | LOCATE(substr, string [,pos]) |
5 | CONVERT(VARCHAR(len), string) | Get a substring of string | CAST(string AS CHAR(len)) |
6 | DATALENGTH(string) | String length in bytes | LENGTH(string) |
7 | LEFT(string, num) | Return num leftmost characters | LEFT(string, num) |
8 | LEN(string) | String length in characters | CHAR_LENGTH(string) |
9 | LOWER(string) | Convert string to lowercase | LOWER(string) |
10 | LTRIM(string) | Remove the leading blanks | LTRIM(string) |
11 | PATINDEX(pattern, string) | Get position of pattern in string | UDF required |
12 | REPLICATE(string, num) | Replicate string num times | REPEAT(string, num) |
13 | REVERSE(string) | Reverse string | REVERSE(string) |
14 | RIGHT(string, num) | Return num rightmost characters | RIGHT(string, num) |
15 | RTRIM(string) | Remove the trailing blanks | RTRIM(string) |
16 | SPACE(num) | Return string of num spaces | SPACE(num) |
17 | STR_REPLACE(string, from, to) | Replace substring with specified value | REPLACE(string, from, to) |
18 | STUFF(str, start, len, val) | Replace substring with val | INSERT(str, start, len, val) |
19 | SUBSTRING(string, start, len) | Get a substring of string | SUBSTRING(string, start, len) |
20 | UPPER(string) | Convert string to uppercase | UPPER(string) |
Datetime functions:
Sybase ASE | MariaDB | ||
1 | CURRENT_BIGDATETIME() | Get the current datetime (microseconds) | NOW(6) |
2 | CURRENT_BIGTIME() | Get the current time (microseconds) | CURTIME(6) |
3 | CURRENT_DATE() | Get the current date (year, month, day) | CURRENT_DATE() |
4 | CURRENT_TIME() | Get the current time (milliseconds) | CURRENT_TIME(3) |
5 | DATEADD(unit, num, datetime) | Add an interval to datetime | TIMESTAMPADD(unit, num, datetime) |
6 | DATEDIFF(unit, dt1, dt2) | Difference between 2 datetimes (dt2 - dt1) | TIMESTAMPDIFF(unit, dt1, dt2) |
7 | DATENAME(unit, datetime) | Extract unit from datetime | YEAR(), MONTH(), DAY() etc. |
8 | DATEPART(unit, datetime) | Extract unit from datetime | YEAR(), MONTH(), DAY() etc. |
9 | DAY(datetime) | Extract day from datetime | DAY(datetime) |
10 | GETDATE() | Get the current datetime (milliseconds) | NOW(3) |
11 | GETUTCDATE() | Get the current UTC datetime (milliseconds) | UTC_TIMESTAMP(3) |
12 | MONTH(datetime) | Extract month from datetime | MONTH(datetime) |
13 | YEAR(datetime) | Extract year from datetime | YEAR(datetime) |
Conversion functions:
Sybase ASE | MariaDB | ||
1 | BIGINTTOHEX(num) | Convert BIGINT to 16-byte 0-padded hex string (without 0x prefix) | LPAD(HEX(num), 16, '0') |
2 | BINTOSTR(exp) | Convert a binary value to hex string | HEX(exp) |
3 | CONVERT(CHAR, datetime, style) | Convert datetime to string | DATE_FORMAT(datetime, format) |
4 | CONVERT(DATETIME, string, style) | Convert string to DATETIME | STR_TO_DATE(string, format) |
5 | HEXTOBIGINT(string) | Convert hex string to BIGINT | CAST(CONV(string,16,10) AS UNSIGNED) |
6 | HEXTOINT(string) | Convert hex string to INT | CAST(CONV(string,16,10) AS UNSIGNED) |
7 | INTTOHEX(num) | Convert INT to 8-byte 0-padded hex string (without 0x prefix) | LPAD(HEX(num), 8, '0') |
CASE and NULL functions:
Sybase ASE | MariaDB | ||
1 | COALESCE(exp, exp2, …) | Get first non-NULL expression | COALESCE(exp, exp2, …) |
2 | ISNULL(exp, exp2) | Return exp2 if exp is NULL | IFNULL(exp, exp2) |
3 | NULLIF(exp, exp2) | Return NULL if exp=exp2, otherwise return exp | NULLIF(exp, exp2) |
Math functions:
Sybase ASE | MariaDB | ||
1 | ABS(num) | Get the absolute value | ABS(num) |
2 | ACOS(num) | Get the arc cosine | ACOS(num) |
3 | ASIN(num) | Get the arc sine | ASIN(num) |
4 | ATAN(num) | Get the arc tangent | ATAN(num) |
5 | ATN2(num) | Get the arc tangent of 2 values | ATAN2(num) |
6 | CEILING(num) | Get the smallest INT greater than or equal to num | CEILING(num) |
7 | COS(num) | Get the cosine | COS(num) |
8 | COT(num) | Get the cotangent | COT(num) |
9 | EXP(num) | Get e raised to the power of num | EXP(num) |
10 | FLOOR(num) | Get the largest INT less than or equal to num | FLOOR(num) |
11 | LOG(num) | Get the natural logarithm of num | LOG(num) |
12 | LOG10(num) | Get the base 10 logarithm of num | LOG10(num) |
13 | PI() | Get the pi constant | PI() |
14 | POWER(num, power) | Raise num to power | POWER(num, power) |
15 | RADIANS(degree) | Convert degree to radians | RADIANS(degree) |
16 | RAND([seed]) | Get random float within 0 and 1.0 | RAND([seed]) |
17 | ROUND(num [,dec]) | Round num to dec digits | ROUND(num [,dec]) |
18 | SIGN(num) | Return -1 for negative and 1 for positive num | SIGN(num) |
19 | SIN(num) | Get the sine | SIN(num) |
20 | SQUARE(num) | Get the square of num | POWER(num, 2) |
21 | SQRT(num) | Get the square root of num | SQRT(num) |
22 | TAN(num) | Get the tangent | TAN(num) |
System functions:
Sybase ASE | MariaDB | |||
1 | ASEHOSTNAME() | Get the server host name | @@HOSTNAME | |
2 | DB_NAME() | Get the database name | DATABASE() | |
3 | HASH(expr, 'MD5' | 'SHA1') | Hash the expr value | MD5(expr) | SHA1(expr) |
4 | NEWID() | Generate a GUID value | UUID() | |
5 | SUSER_NAME() | Get the user name | CURRENT_USER() | |
6 | USER | USER_NAME() |
Other functions:
Sybase ASE | MariaDB | |||
1 | SOUNDEX(string) | Get four-character soundex code for string | SOUNDEX(string) |
CREATE TABLE statement keywords and clauses:
Sybase ASE | MariaDB | |||
1 | IDENTITY | Identity column | AUTO_INCREMENT | Integer column and primary key is required in CREATE TABLE |
2 | ENCRYPT | Encrypted column | Keyword removed |
UPDATE statement keywords and clauses:
Sybase ASE | MariaDB | ||
1 | UPDATE t1 SET … FROM t1, t2, … WHERE … | UPDATE FROM syntax | UPDATE t1, t2, … SET … WHERE … |
Converting Transact-SQL stored procedures:
For further information, see Conversion of Transact-SQL Statements.
Variable declaration and assignment:
Sybase ASE | MariaDB | |
1 | DECLARE in any order, can be placed before/after DML/DDL statements | Must be before DML/DDL, cursors must be declared after variables, handlers after cursors |
2 | DECLARE @var type | DECLARE v_var type |
3 | DECLARE @v1 type1, @v2 type2, … | DECLARE v_v1 type1; DECLARE v_v2 type2, DECLARE … |
4 | DECLARE cur CURSOR FOR select_stmt | DECLARE cur CURSOR FOR select_stmt |
5 | SET @var = expression | SET v_var = expression |
6 | SELECT @var = expression | SET v_var = expression |
Flow-of-control statements:
Sybase ASE | MariaDB | ||
1 | IF condition BEGIN … END | IF statement | IF condition THEN … END IF; |
2 | IF … ELSE IF … | IF ELSE IF statement | IF … ELSEIF … |
3 | WHILE condition BEGIN stmts END | Conditional loop | WHILE condition DO stmts END WHILE; |
4 | RETURN | RETURN from a stored procedure | LEAVE sp_lbl |
Cursor operations:
Sybase ASE | MariaDB | |||
1 | @@sqlstatus = 0 | Fetch was successful | not_found = 0 | with condition handler for NOT FOUND |
@@sqlstatus = 2 | No more data in the result set | not_found = 1 |
Executing stored procedures:
Sybase ASE | MariaDB | ||
1 | EXEC sp_name @par1, @par2 [OUTPUT], … | Executing procedure with parameters | CALL sp_name(par1, par2, …) |
Temporary tables:
Sybase ASE | MariaDB | ||
1 | CREATE TABLE tempdb..name | Create a temporary table | CREATE TEMPORARY TABLE name |
Transaction statements:
Sybase ASE | MariaDB | ||
1 | BEGIN TRANSACTION | Begin a transaction | START TRANSACTION |
2 | COMMIT TRANSACTION | TRAN | Commit transaction | COMMIT |
Other issues:
Sybase ASE | MariaDB | |||
1 | BEGIN … END | Code block | BEGIN … END; | semicolon (;) is required |