Repository: tobymao/sqlglot

Issue:
Converstion of snowflake POSITION to Trino STRPOS Is incorrect 
When translating Snowflake dialect to Trino with the POSITION function it has a 3rd parameter allowing you to define the start position in the  string from which you can start looking for the expression where as in trino the 3rd argument in STRPOS in not the starting point in which to look in the string but the the N Instance of the character you are looking for.

document reference:
1. Snowflake POSITION - https://docs.snowflake.com/en/sql-reference/functions/position
2. Trino STRPOS - https://trino.io/docs/current/functions/string.html#strpos:~:text=0%20is%20returned.-,strpos,-(string%2C


Reproducible code : 

snowflake:
``` 
SELECT POSITION( 'A','ADFGAC', 3) => Returns position 5
```

Trino:
``` 
SELECT STRPOS( 'A','ADFGAC', 3) => Returns position 0 - looks for the 3 acurance of A
```

Discussion:

Patch:
diff --git a/sqlglot/__init__.py b/sqlglot/__init__.py
index e30232c7..756532f6 100644
--- a/sqlglot/__init__.py
+++ b/sqlglot/__init__.py
@@ -45,7 +45,7 @@ from sqlglot.expressions import (
 from sqlglot.generator import Generator as Generator
 from sqlglot.parser import Parser as Parser
 from sqlglot.schema import MappingSchema as MappingSchema, Schema as Schema
-from sqlglot.tokens import Tokenizer as Tokenizer, TokenType as TokenType
+from sqlglot.tokens import Token as Token, Tokenizer as Tokenizer, TokenType as TokenType
 
 if t.TYPE_CHECKING:
     from sqlglot._typing import E
@@ -69,6 +69,21 @@ schema = MappingSchema()
 """The default schema used by SQLGlot (e.g. in the optimizer)."""
 
 
+def tokenize(sql: str, read: DialectType = None, dialect: DialectType = None) -> t.List[Token]:
+    """
+    Tokenizes the given SQL string.
+
+    Args:
+        sql: the SQL code string to tokenize.
+        read: the SQL dialect to apply during tokenizing (eg. "spark", "hive", "presto", "mysql").
+        dialect: the SQL dialect (alias for read).
+
+    Returns:
+        The resulting list of tokens.
+    """
+    return Dialect.get_or_raise(read or dialect).tokenize(sql)
+
+
 def parse(
     sql: str, read: DialectType = None, dialect: DialectType = None, **opts
 ) -> t.List[t.Optional[Expression]]:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 80e5772a..b446e2a9 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -246,16 +246,6 @@ class BigQuery(Dialect):
         "%E6S": "%S.%f",
     }
 
-    ESCAPE_SEQUENCES = {
-        "\\a": "\a",
-        "\\b": "\b",
-        "\\f": "\f",
-        "\\n": "\n",
-        "\\r": "\r",
-        "\\t": "\t",
-        "\\v": "\v",
-    }
-
     FORMAT_MAPPING = {
         "DD": "%d",
         "MM": "%m",
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 874ce901..631dc304 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -50,7 +50,7 @@ class ClickHouse(Dialect):
     SAFE_DIVISION = True
     LOG_BASE_FIRST: t.Optional[bool] = None
 
-    ESCAPE_SEQUENCES = {
+    UNESCAPED_SEQUENCES = {
         "\\0": "\0",
     }
 
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 1fe0d5c6..f6901510 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -110,8 +110,6 @@ class _Dialect(type):
         klass.INVERSE_TIME_MAPPING = {v: k for k, v in klass.TIME_MAPPING.items()}
         klass.INVERSE_TIME_TRIE = new_trie(klass.INVERSE_TIME_MAPPING)
 
-        klass.INVERSE_ESCAPE_SEQUENCES = {v: k for k, v in klass.ESCAPE_SEQUENCES.items()}
-
         base = seq_get(bases, 0)
         base_tokenizer = (getattr(base, "tokenizer_class", Tokenizer),)
         base_parser = (getattr(base, "parser_class", Parser),)
@@ -145,6 +143,21 @@ class _Dialect(type):
         klass.BYTE_START, klass.BYTE_END = get_start_end(TokenType.BYTE_STRING)
         klass.UNICODE_START, klass.UNICODE_END = get_start_end(TokenType.UNICODE_STRING)
 
+        if "\\" in klass.tokenizer_class.STRING_ESCAPES:
+            klass.UNESCAPED_SEQUENCES = {
+                "\\a": "\a",
+                "\\b": "\b",
+                "\\f": "\f",
+                "\\n": "\n",
+                "\\r": "\r",
+                "\\t": "\t",
+                "\\v": "\v",
+                "\\\\": "\\",
+                **klass.UNESCAPED_SEQUENCES,
+            }
+
+        klass.ESCAPED_SEQUENCES = {v: k for k, v in klass.UNESCAPED_SEQUENCES.items()}
+
         if enum not in ("", "bigquery"):
             klass.generator_class.SELECT_KINDS = ()
 
@@ -247,8 +260,8 @@ class Dialect(metaclass=_Dialect):
     If empty, the corresponding trie will be constructed off of `TIME_MAPPING`.
     """
 
-    ESCAPE_SEQUENCES: t.Dict[str, str] = {}
-    """Mapping of an unescaped escape sequence to the corresponding character."""
+    UNESCAPED_SEQUENCES: t.Dict[str, str] = {}
+    """Mapping of an escaped sequence (`\\n`) to its unescaped version (`\n`)."""
 
     PSEUDOCOLUMNS: t.Set[str] = set()
     """
@@ -287,7 +300,7 @@ class Dialect(metaclass=_Dialect):
     INVERSE_TIME_MAPPING: t.Dict[str, str] = {}
     INVERSE_TIME_TRIE: t.Dict = {}
 
-    INVERSE_ESCAPE_SEQUENCES: t.Dict[str, str] = {}
+    ESCAPED_SEQUENCES: t.Dict[str, str] = {}
 
     # Delimiters for string literals and identifiers
     QUOTE_START = "'"
@@ -608,13 +621,21 @@ def no_map_from_entries_sql(self: Generator, expression: exp.MapFromEntries) ->
     return ""
 
 
-def str_position_sql(self: Generator, expression: exp.StrPosition) -> str:
+def str_position_sql(
+    self: Generator, expression: exp.StrPosition, generate_instance: bool = False
+) -> str:
     this = self.sql(expression, "this")
     substr = self.sql(expression, "substr")
     position = self.sql(expression, "position")
+    instance = expression.args.get("instance") if generate_instance else None
+    position_offset = ""
+
     if position:
-        return f"STRPOS(SUBSTR({this}, {position}), {substr}) + {position} - 1"
-    return f"STRPOS({this}, {substr})"
+        # Normalize third 'pos' argument into 'SUBSTR(..) + offset' across dialects
+        this = self.func("SUBSTR", this, position)
+        position_offset = f" + {position} - 1"
+
+    return self.func("STRPOS", this, substr, instance) + position_offset
 
 
 def struct_extract_sql(self: Generator, expression: exp.StructExtract) -> str:
diff --git a/sqlglot/dialects/drill.py b/sqlglot/dialects/drill.py
index c1f6afa8..4a11a196 100644
--- a/sqlglot/dialects/drill.py
+++ b/sqlglot/dialects/drill.py
@@ -84,7 +84,6 @@ class Drill(Dialect):
 
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
-            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "drill"),
             "TO_TIMESTAMP": exp.TimeStrToTime.from_arg_list,
             "TO_CHAR": build_formatted_time(exp.TimeToStr, "drill"),
         }
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index c1580e34..86c9b618 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -22,6 +22,7 @@ from sqlglot.dialects.dialect import (
     rename_func,
     right_to_substring_sql,
     struct_extract_sql,
+    str_position_sql,
     timestamptrunc_sql,
     timestrtotime_sql,
     ts_or_ds_add_cast,
@@ -401,7 +402,7 @@ class Presto(Dialect):
                 ]
             ),
             exp.SortArray: _no_sort_array,
-            exp.StrPosition: rename_func("STRPOS"),
+            exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True),
             exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
             exp.StrToMap: rename_func("SPLIT_TO_MAP"),
             exp.StrToTime: _str_to_time_sql,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index ae4b1ccd..906bdd8c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1113,7 +1113,7 @@ class Generator(metaclass=_Generator):
         return f"{self.dialect.QUOTE_START}{this}{self.dialect.QUOTE_END}"
 
     def rawstring_sql(self, expression: exp.RawString) -> str:
-        string = self.escape_str(expression.this.replace("\\", "\\\\"))
+        string = self.escape_str(expression.this.replace("\\", "\\\\"), escape_backslash=False)
         return f"{self.dialect.QUOTE_START}{string}{self.dialect.QUOTE_END}"
 
     def datatypeparam_sql(self, expression: exp.DataTypeParam) -> str:
@@ -1587,17 +1587,17 @@ class Generator(metaclass=_Generator):
         return f"{self.seg('RETURNING')} {self.expressions(expression, flat=True)}"
 
     def rowformatdelimitedproperty_sql(self, expression: exp.RowFormatDelimitedProperty) -> str:
-        fields = expression.args.get("fields")
+        fields = self.sql(expression, "fields")
         fields = f" FIELDS TERMINATED BY {fields}" if fields else ""
-        escaped = expression.args.get("escaped")
+        escaped = self.sql(expression, "escaped")
         escaped = f" ESCAPED BY {escaped}" if escaped else ""
-        items = expression.args.get("collection_items")
+        items = self.sql(expression, "collection_items")
         items = f" COLLECTION ITEMS TERMINATED BY {items}" if items else ""
-        keys = expression.args.get("map_keys")
+        keys = self.sql(expression, "map_keys")
         keys = f" MAP KEYS TERMINATED BY {keys}" if keys else ""
-        lines = expression.args.get("lines")
+        lines = self.sql(expression, "lines")
         lines = f" LINES TERMINATED BY {lines}" if lines else ""
-        null = expression.args.get("null")
+        null = self.sql(expression, "null")
         null = f" NULL DEFINED AS {null}" if null else ""
         return f"ROW FORMAT DELIMITED{fields}{escaped}{items}{keys}{lines}{null}"
 
@@ -2001,13 +2001,17 @@ class Generator(metaclass=_Generator):
             text = f"{self.dialect.QUOTE_START}{self.escape_str(text)}{self.dialect.QUOTE_END}"
         return text
 
-    def escape_str(self, text: str) -> str:
-        text = text.replace(self.dialect.QUOTE_END, self._escaped_quote_end)
-        if self.dialect.INVERSE_ESCAPE_SEQUENCES:
-            text = "".join(self.dialect.INVERSE_ESCAPE_SEQUENCES.get(ch, ch) for ch in text)
-        elif self.pretty:
+    def escape_str(self, text: str, escape_backslash: bool = True) -> str:
+        if self.dialect.ESCAPED_SEQUENCES:
+            to_escaped = self.dialect.ESCAPED_SEQUENCES
+            text = "".join(
+                to_escaped.get(ch, ch) if escape_backslash or ch != "\\" else ch for ch in text
+            )
+
+        if self.pretty:
             text = text.replace("\n", self.SENTINEL_LINE_BREAK)
-        return text
+
+        return text.replace(self.dialect.QUOTE_END, self._escaped_quote_end)
 
     def loaddata_sql(self, expression: exp.LoadData) -> str:
         local = " LOCAL" if expression.args.get("local") else ""
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 1c5f8bc9..7f0cb5d8 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -522,6 +522,7 @@ class _Tokenizer(type):
                 break_=_TOKEN_TYPE_TO_INDEX[TokenType.BREAK],
                 dcolon=_TOKEN_TYPE_TO_INDEX[TokenType.DCOLON],
                 heredoc_string=_TOKEN_TYPE_TO_INDEX[TokenType.HEREDOC_STRING],
+                raw_string=_TOKEN_TYPE_TO_INDEX[TokenType.RAW_STRING],
                 hex_string=_TOKEN_TYPE_TO_INDEX[TokenType.HEX_STRING],
                 identifier=_TOKEN_TYPE_TO_INDEX[TokenType.IDENTIFIER],
                 number=_TOKEN_TYPE_TO_INDEX[TokenType.NUMBER],
@@ -922,7 +923,7 @@ class Tokenizer(metaclass=_Tokenizer):
 
         if USE_RS_TOKENIZER:
             self._rs_dialect_settings = RsTokenizerDialectSettings(
-                escape_sequences=self.dialect.ESCAPE_SEQUENCES,
+                unescaped_sequences=self.dialect.UNESCAPED_SEQUENCES,
                 identifiers_can_start_with_digit=self.dialect.IDENTIFIERS_CAN_START_WITH_DIGIT,
             )
 
@@ -1272,7 +1273,9 @@ class Tokenizer(metaclass=_Tokenizer):
                     tag = ""
                 else:
                     tag = self._extract_string(
-                        end, raise_unmatched=not self.HEREDOC_TAG_IS_IDENTIFIER
+                        end,
+                        unescape_sequences=False,
+                        raise_unmatched=not self.HEREDOC_TAG_IS_IDENTIFIER,
                     )
 
                 if self._end and tag and self.HEREDOC_TAG_IS_IDENTIFIER:
@@ -1285,7 +1288,7 @@ class Tokenizer(metaclass=_Tokenizer):
             return False
 
         self._advance(len(start))
-        text = self._extract_string(end)
+        text = self._extract_string(end, unescape_sequences=token_type != TokenType.RAW_STRING)
 
         if base:
             try:
@@ -1318,13 +1321,28 @@ class Tokenizer(metaclass=_Tokenizer):
         )
 
     def _extract_string(
-        self, delimiter: str, escapes: t.Optional[t.Set[str]] = None, raise_unmatched: bool = True
+        self,
+        delimiter: str,
+        escapes: t.Optional[t.Set[str]] = None,
+        unescape_sequences: bool = True,
+        raise_unmatched: bool = True,
     ) -> str:
         text = ""
         delim_size = len(delimiter)
         escapes = self._STRING_ESCAPES if escapes is None else escapes
 
         while True:
+            if (
+                unescape_sequences
+                and self.dialect.UNESCAPED_SEQUENCES
+                and self._peek
+                and self._char in self.STRING_ESCAPES
+            ):
+                unescaped_sequence = self.dialect.UNESCAPED_SEQUENCES.get(self._char + self._peek)
+                if unescaped_sequence:
+                    self._advance(2)
+                    text += unescaped_sequence
+                    continue
             if (
                 self._char in escapes
                 and (self._peek == delimiter or self._peek in escapes)
@@ -1351,17 +1369,6 @@ class Tokenizer(metaclass=_Tokenizer):
 
                     raise TokenError(f"Missing {delimiter} from {self._line}:{self._start}")
 
-                if (
-                    self.dialect.ESCAPE_SEQUENCES
-                    and self._peek
-                    and self._char in self.STRING_ESCAPES
-                ):
-                    escaped_sequence = self.dialect.ESCAPE_SEQUENCES.get(self._char + self._peek)
-                    if escaped_sequence:
-                        self._advance(2)
-                        text += escaped_sequence
-                        continue
-
                 current = self._current - 1
                 self._advance(alnum=True)
                 text += self.sql[current : self._current - 1]
diff --git a/sqlglotrs/src/settings.rs b/sqlglotrs/src/settings.rs
index c6e76a70..4cacb9b5 100644
--- a/sqlglotrs/src/settings.rs
+++ b/sqlglotrs/src/settings.rs
@@ -10,6 +10,7 @@ pub struct TokenTypeSettings {
     pub break_: TokenType,
     pub dcolon: TokenType,
     pub heredoc_string: TokenType,
+    pub raw_string: TokenType,
     pub hex_string: TokenType,
     pub identifier: TokenType,
     pub number: TokenType,
@@ -28,6 +29,7 @@ impl TokenTypeSettings {
         break_: TokenType,
         dcolon: TokenType,
         heredoc_string: TokenType,
+        raw_string: TokenType,
         hex_string: TokenType,
         identifier: TokenType,
         number: TokenType,
@@ -42,6 +44,7 @@ impl TokenTypeSettings {
             break_,
             dcolon,
             heredoc_string,
+            raw_string,
             hex_string,
             identifier,
             number,
@@ -151,7 +154,7 @@ impl TokenizerSettings {
 #[derive(Clone, Debug)]
 #[pyclass]
 pub struct TokenizerDialectSettings {
-    pub escape_sequences: HashMap<String, String>,
+    pub unescaped_sequences: HashMap<String, String>,
     pub identifiers_can_start_with_digit: bool,
 }
 
@@ -159,11 +162,11 @@ pub struct TokenizerDialectSettings {
 impl TokenizerDialectSettings {
     #[new]
     pub fn new(
-        escape_sequences: HashMap<String, String>,
+        unescaped_sequences: HashMap<String, String>,
         identifiers_can_start_with_digit: bool,
     ) -> Self {
         TokenizerDialectSettings {
-            escape_sequences,
+            unescaped_sequences,
             identifiers_can_start_with_digit,
         }
     }
diff --git a/sqlglotrs/src/tokenizer.rs b/sqlglotrs/src/tokenizer.rs
index e77b6a84..cdc61d70 100644
--- a/sqlglotrs/src/tokenizer.rs
+++ b/sqlglotrs/src/tokenizer.rs
@@ -423,7 +423,7 @@ impl<'a> TokenizerState<'a> {
                 let tag = if self.current_char.to_string() == *end {
                     String::from("")
                 } else {
-                    self.extract_string(end, false, !self.settings.heredoc_tag_is_identifier)?
+                    self.extract_string(end, false, false, !self.settings.heredoc_tag_is_identifier)?
                 };
 
                 if self.is_end && !tag.is_empty() && self.settings.heredoc_tag_is_identifier {
@@ -441,7 +441,7 @@ impl<'a> TokenizerState<'a> {
         };
 
         self.advance(start.len() as isize)?;
-        let text = self.extract_string(&end, false, true)?;
+        let text = self.extract_string(&end, false, token_type != self.token_types.raw_string, true)?;
 
         if let Some(b) = base {
             if u64::from_str_radix(&text, b).is_err() {
@@ -584,7 +584,7 @@ impl<'a> TokenizerState<'a> {
 
     fn scan_identifier(&mut self, identifier_end: &str) -> Result<(), TokenizerError> {
         self.advance(1)?;
-        let text = self.extract_string(identifier_end, true, true)?;
+        let text = self.extract_string(identifier_end, true, true, true)?;
         self.add(self.token_types.identifier, Some(text))
     }
 
@@ -592,6 +592,7 @@ impl<'a> TokenizerState<'a> {
         &mut self,
         delimiter: &str,
         use_identifier_escapes: bool,
+        unescape_sequences: bool,
         raise_unmatched: bool,
     ) -> Result<String, TokenizerError> {
         let mut text = String::from("");
@@ -602,8 +603,23 @@ impl<'a> TokenizerState<'a> {
             } else {
                 &self.settings.string_escapes
             };
-
             let peek_char_str = self.peek_char.to_string();
+
+            if unescape_sequences
+                && !self.dialect_settings.unescaped_sequences.is_empty()
+                && !self.peek_char.is_whitespace()
+                && self.settings.string_escapes.contains(&self.current_char)
+            {
+                let sequence_key = format!("{}{}", self.current_char, self.peek_char);
+                if let Some(unescaped_sequence) =
+                    self.dialect_settings.unescaped_sequences.get(&sequence_key)
+                {
+                    self.advance(2)?;
+                    text.push_str(unescaped_sequence);
+                    continue;
+                }
+            }
+
             if escapes.contains(&self.current_char)
                 && (peek_char_str == delimiter || escapes.contains(&self.peek_char))
                 && (self.current_char == self.peek_char
@@ -645,20 +661,6 @@ impl<'a> TokenizerState<'a> {
                     ));
                 }
 
-                if !self.dialect_settings.escape_sequences.is_empty()
-                    && !self.peek_char.is_whitespace()
-                    && self.settings.string_escapes.contains(&self.current_char)
-                {
-                    let sequence_key = format!("{}{}", self.current_char, self.peek_char);
-                    if let Some(escaped_sequence) =
-                        self.dialect_settings.escape_sequences.get(&sequence_key)
-                    {
-                        self.advance(2)?;
-                        text.push_str(escaped_sequence);
-                        continue;
-                    }
-                }
-
                 let current = self.current - 1;
                 self.advance(1)?;
                 text.push_str(


Test patch:
diff --git a/tests/dataframe/unit/test_functions.py b/tests/dataframe/unit/test_functions.py
index b5067ece..884cded7 100644
--- a/tests/dataframe/unit/test_functions.py
+++ b/tests/dataframe/unit/test_functions.py
@@ -1156,17 +1156,17 @@ class TestFunctions(unittest.TestCase):
 
     def test_regexp_extract(self):
         col_str = SF.regexp_extract("cola", r"(\d+)-(\d+)", 1)
-        self.assertEqual("REGEXP_EXTRACT(cola, '(\\d+)-(\\d+)', 1)", col_str.sql())
+        self.assertEqual("REGEXP_EXTRACT(cola, '(\\\\d+)-(\\\\d+)', 1)", col_str.sql())
         col = SF.regexp_extract(SF.col("cola"), r"(\d+)-(\d+)", 1)
-        self.assertEqual("REGEXP_EXTRACT(cola, '(\\d+)-(\\d+)', 1)", col.sql())
+        self.assertEqual("REGEXP_EXTRACT(cola, '(\\\\d+)-(\\\\d+)', 1)", col.sql())
         col_no_idx = SF.regexp_extract(SF.col("cola"), r"(\d+)-(\d+)")
-        self.assertEqual("REGEXP_EXTRACT(cola, '(\\d+)-(\\d+)')", col_no_idx.sql())
+        self.assertEqual("REGEXP_EXTRACT(cola, '(\\\\d+)-(\\\\d+)')", col_no_idx.sql())
 
     def test_regexp_replace(self):
         col_str = SF.regexp_replace("cola", r"(\d+)", "--")
-        self.assertEqual("REGEXP_REPLACE(cola, '(\\d+)', '--')", col_str.sql())
+        self.assertEqual("REGEXP_REPLACE(cola, '(\\\\d+)', '--')", col_str.sql())
         col = SF.regexp_replace(SF.col("cola"), r"(\d+)", "--")
-        self.assertEqual("REGEXP_REPLACE(cola, '(\\d+)', '--')", col.sql())
+        self.assertEqual("REGEXP_REPLACE(cola, '(\\\\d+)', '--')", col.sql())
 
     def test_initcap(self):
         col_str = SF.initcap("cola")
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index f4af3f53..300d492a 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -738,10 +738,10 @@ class TestBigQuery(Validator):
         self.validate_all(
             "'\\\\'",
             write={
-                "bigquery": r"'\\'",
-                "duckdb": r"'\\'",
-                "presto": r"'\\'",
-                "hive": r"'\\'",
+                "bigquery": "'\\\\'",
+                "duckdb": "'\\'",
+                "presto": "'\\'",
+                "hive": "'\\\\'",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 3c3a1c62..faab290d 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1510,7 +1510,7 @@ class TestDialect(Validator):
             "POSITION(needle, haystack, pos)",
             write={
                 "drill": "STRPOS(SUBSTR(haystack, pos), needle) + pos - 1",
-                "presto": "STRPOS(haystack, needle, pos)",
+                "presto": "STRPOS(SUBSTR(haystack, pos), needle) + pos - 1",
                 "spark": "LOCATE(needle, haystack, pos)",
                 "clickhouse": "position(haystack, needle, pos)",
                 "snowflake": "POSITION(needle, haystack, pos)",
diff --git a/tests/dialects/test_drill.py b/tests/dialects/test_drill.py
index 41c02fbd..634c247d 100644
--- a/tests/dialects/test_drill.py
+++ b/tests/dialects/test_drill.py
@@ -5,68 +5,17 @@ class TestDrill(Validator):
     dialect = "drill"
 
     def test_drill(self):
-        self.validate_all(
-            "DATE_FORMAT(a, 'yyyy')",
-            write={"drill": "TO_CHAR(a, 'yyyy')"},
+        self.validate_identity(
+            "SELECT * FROM table(dfs.`test_data.xlsx`(type => 'excel', sheetName => 'secondSheet'))"
+        )
+        self.validate_identity(
+            "SELECT * FROM (SELECT * FROM t) PIVOT(avg(c1) AS ac1 FOR c2 IN ('V' AS v))",
         )
 
-    def test_string_literals(self):
         self.validate_all(
             "SELECT '2021-01-01' + INTERVAL 1 MONTH",
             write={
+                "drill": "SELECT '2021-01-01' + INTERVAL '1' MONTH",
                 "mysql": "SELECT '2021-01-01' + INTERVAL '1' MONTH",
             },
         )
-
-    def test_quotes(self):
-        self.validate_all(
-            "'\\''",
-            write={
-                "duckdb": "''''",
-                "presto": "''''",
-                "hive": "'\\''",
-                "spark": "'\\''",
-            },
-        )
-        self.validate_all(
-            "'\"x\"'",
-            write={
-                "duckdb": "'\"x\"'",
-                "presto": "'\"x\"'",
-                "hive": "'\"x\"'",
-                "spark": "'\"x\"'",
-            },
-        )
-        self.validate_all(
-            "'\\\\a'",
-            read={
-                "presto": "'\\\\a'",
-            },
-            write={
-                "duckdb": "'\\\\a'",
-                "presto": "'\\\\a'",
-                "hive": "'\\\\a'",
-                "spark": "'\\\\a'",
-            },
-        )
-
-    def test_table_function(self):
-        self.validate_all(
-            "SELECT * FROM table( dfs.`test_data.xlsx` (type => 'excel', sheetName => 'secondSheet'))",
-            write={
-                "drill": "SELECT * FROM table(dfs.`test_data.xlsx`(type => 'excel', sheetName => 'secondSheet'))",
-            },
-        )
-
-    def test_validate_pivot(self):
-        self.validate_all(
-            "SELECT * FROM (SELECT education_level, salary, marital_status, "
-            "EXTRACT(year FROM age(birth_date)) age FROM cp.`employee.json`) PIVOT (avg(salary) AS "
-            "avg_salary, avg(age) AS avg_age FOR marital_status IN ('M' married, 'S' single))",
-            write={
-                "drill": "SELECT * FROM (SELECT education_level, salary, marital_status, "
-                "EXTRACT(year FROM age(birth_date)) AS age FROM cp.`employee.json`) "
-                "PIVOT(avg(salary) AS avg_salary, avg(age) AS avg_age FOR marital_status "
-                "IN ('M' AS married, 'S' AS single))"
-            },
-        )
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 04326d30..33294ee0 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -235,15 +235,18 @@ class TestHive(Validator):
             },
         )
         self.validate_all(
-            "'\\\\a'",
+            "'\\\\\\\\a'",
             read={
+                "drill": "'\\\\\\\\a'",
+                "duckdb": "'\\\\a'",
                 "presto": "'\\\\a'",
             },
             write={
+                "drill": "'\\\\\\\\a'",
                 "duckdb": "'\\\\a'",
+                "hive": "'\\\\\\\\a'",
                 "presto": "'\\\\a'",
-                "hive": "'\\\\a'",
-                "spark": "'\\\\a'",
+                "spark": "'\\\\\\\\a'",
             },
         )
 
@@ -563,7 +566,7 @@ class TestHive(Validator):
             "LOCATE('a', x, 3)",
             write={
                 "duckdb": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
-                "presto": "STRPOS(x, 'a', 3)",
+                "presto": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
                 "hive": "LOCATE('a', x, 3)",
                 "spark": "LOCATE('a', x, 3)",
             },
@@ -736,13 +739,12 @@ class TestHive(Validator):
         )
 
     def test_escapes(self) -> None:
-        self.validate_identity("'\n'")
+        self.validate_identity("'\n'", "'\\n'")
         self.validate_identity("'\\n'")
-        self.validate_identity("'\\\n'")
+        self.validate_identity("'\\\n'", "'\\\\\\n'")
         self.validate_identity("'\\\\n'")
         self.validate_identity("''")
         self.validate_identity("'\\\\'")
-        self.validate_identity("'\\z'")
         self.validate_identity("'\\\\z'")
 
     def test_data_type(self):
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 99b1fc08..21624990 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -177,6 +177,17 @@ class TestPresto(Validator):
                 "spark": "ARRAY_JOIN(x, '-', 'a')",
             },
         )
+        self.validate_all(
+            "STRPOS('ABC', 'A', 3)",
+            read={
+                "trino": "STRPOS('ABC', 'A', 3)",
+            },
+            write={
+                "presto": "STRPOS('ABC', 'A', 3)",
+                "trino": "STRPOS('ABC', 'A', 3)",
+                "snowflake": "POSITION('A', 'ABC')",
+            },
+        )
 
     def test_interval_plural_to_singular(self):
         # Microseconds, weeks and quarters are not supported in Presto/Trino INTERVAL literals
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 7affe31f..a91f4f9f 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -212,18 +212,6 @@ class TestRedshift(Validator):
                 "redshift": "SELECT CAST('abc' AS CHAR)",
             },
         )
-        self.validate_all(
-            "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid",
-            write={
-                "redshift": "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid",
-            },
-        )
-        self.validate_all(
-            'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5',
-            write={
-                "redshift": 'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5'
-            },
-        )
         self.validate_all(
             "SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
             write={
@@ -315,6 +303,12 @@ class TestRedshift(Validator):
         self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)")
         self.validate_identity("SELECT APPROXIMATE AS y")
         self.validate_identity("CREATE TABLE t (c BIGINT IDENTITY(0, 1))")
+        self.validate_identity(
+            "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid"
+        )
+        self.validate_identity(
+            """SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE '%start\\\\_%' LIMIT 5"""
+        )
         self.validate_identity(
             """SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)"""
         )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 69c03bac..a41d35a0 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -89,8 +89,8 @@ WHERE
         self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
         self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
-        self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')")
         self.validate_identity("ALTER TABLE a SWAP WITH b")
+        self.validate_identity("SELECT MATCH_CONDITION")
         self.validate_identity(
             'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
         )
@@ -100,7 +100,10 @@ WHERE
         self.validate_identity(
             "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
         )
-        self.validate_identity("SELECT MATCH_CONDITION")
+        self.validate_identity(
+            "REGEXP_REPLACE('target', 'pattern', '\n')",
+            "REGEXP_REPLACE('target', 'pattern', '\\n')",
+        )
         self.validate_identity(
             "SELECT a:from::STRING, a:from || ' test' ",
             "SELECT CAST(GET_PATH(a, 'from') AS TEXT), GET_PATH(a, 'from') || ' test'",
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 88c9ab4b..18f1fb73 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -284,6 +284,18 @@ TBLPROPERTIES (
             "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",
         )
 
+        self.validate_all(
+            "SELECT SPLIT('123|789', '\\\\|')",
+            read={
+                "duckdb": "SELECT STR_SPLIT_REGEX('123|789', '\\|')",
+                "presto": "SELECT REGEXP_SPLIT('123|789', '\\|')",
+            },
+            write={
+                "duckdb": "SELECT STR_SPLIT_REGEX('123|789', '\\|')",
+                "presto": "SELECT REGEXP_SPLIT('123|789', '\\|')",
+                "spark": "SELECT SPLIT('123|789', '\\\\|')",
+            },
+        )
         self.validate_all(
             "WITH tbl AS (SELECT 1 AS id, 'eggy' AS name UNION ALL SELECT NULL AS id, 'jake' AS name) SELECT COUNT(DISTINCT id, name) AS cnt FROM tbl",
             write={
@@ -648,10 +660,10 @@ TBLPROPERTIES (
             "SELECT TRANSFORM(zip_code, name, age) USING 'cat' AS (a STRING, b STRING, c STRING) FROM person WHERE zip_code > 94511"
         )
         self.validate_identity(
-            "SELECT TRANSFORM(name, age) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' USING 'cat' AS (name_age STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' FROM person"
+            "SELECT TRANSFORM(name, age) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' NULL DEFINED AS 'NULL' USING 'cat' AS (name_age STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' LINES TERMINATED BY '\\n' NULL DEFINED AS 'NULL' FROM person"
         )
         self.validate_identity(
-            "SELECT TRANSFORM(zip_code, name, age) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') USING 'cat' AS (a STRING, b STRING, c STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') FROM person WHERE zip_code > 94511"
+            "SELECT TRANSFORM(zip_code, name, age) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\\t') USING 'cat' AS (a STRING, b STRING, c STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\\t') FROM person WHERE zip_code > 94511"
         )
         self.validate_identity(
             "SELECT TRANSFORM(zip_code, name, age) USING 'cat' FROM person WHERE zip_code > 94500"