SQLines 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() | USER() |
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 |