sql-dialect-translator

1. IDENTIFY    -> Detect source dialect
2. ANALYZE     -> Find dialect-specific features
3. MAP         -> Identify equivalent features in target
4. TRANSLATE   -> Convert syntax
5. VALIDATE    -> Verify correctness
6. OPTIMIZE    -> Apply target dialect best practices

INSERT INTO table (id, name) VALUES (1, 'test')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

INSERT INTO table (id, name) VALUES (1, 'test')
ON DUPLICATE KEY UPDATE name = VALUES(name);

MERGE INTO table AS target
USING (VALUES (1, 'test')) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

MERGE INTO table target
USING (SELECT 1 AS id, 'test' AS name FROM dual) source
ON (target.id = source.id)
WHEN MATCHED THEN UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

WITH cte AS (
    SELECT ...
)
SELECT * FROM cte;

SELECT data->>'name' FROM table;
SELECT data->'nested'->>'field' FROM table;
SELECT data @> '{"key": "value"}' FROM table;

SELECT JSON_EXTRACT(data, '$.name') FROM table;
SELECT data->'$.name' FROM table;
SELECT JSON_CONTAINS(data, '"value"', '$.key') FROM table;

SELECT JSON_VALUE(data, '$.name') FROM table;
SELECT JSON_QUERY(data, '$.nested') FROM table;

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE users (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100)
);

SELECT first_name || ' ' || last_name AS full_name FROM users;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

SELECT first_name + ' ' + last_name AS full_name FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

SELECT created_at + INTERVAL '30 days' FROM users;
SELECT DATE_TRUNC('month', created_at) FROM users;

SELECT DATE_ADD(created_at, INTERVAL 30 DAY) FROM users;
SELECT DATE_FORMAT(created_at, '%Y-%m-01') FROM users;

SELECT DATEADD(day, 30, created_at) FROM users;
SELECT DATETRUNC(month, created_at) FROM users;  -- SQL Server 2022+
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) FROM users;  -- Earlier

SELECT created_at + 30 FROM users;
SELECT TRUNC(created_at, 'MM') FROM users;

SELECT * FROM users WHERE is_active = TRUE;

SELECT * FROM users WHERE is_active = 1;
SELECT * FROM users WHERE is_active = TRUE;  -- Also works

SELECT * FROM users WHERE is_active = 1;

SELECT * FROM users WHERE is_active = 1;

pip install sqlglot
python scripts/translate_sql.py query.sql --from mysql --to postgres
echo "SELECT TOP 10 * FROM t" | python scripts/translate_sql.py - --from tsql --to oracle