Repository: sqlfluff/sqlfluff

Issue:
pysparksql: Unable to lex characters '${db}'
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

When running sqlfluff lint . against a pyspark sql file generated from a Databricks notebook, receive an LXR error and PRS error with widget parameters.

Is there a way to ignore errors or update lexer?  It is valid Databricks pyspark code.


### Expected Behaviour

Code is formatted without error.  This could be an optional warning.

### Observed Behaviour

```plaintext
L:   6 | P:   1 |  PRS | Line 6, Position 1: Found unparsable section: 'CREATE
                       | WIDGET TEXT partition_day DEFAULT...'
L:  20 | P:  20 |  PRS | Line 20, Position 20: Found unparsable section: ' =
                       | ${partition_day}'
L:  20 | P:  23 |  LXR | Unable to lex characters: '${partitio...'
```

### How to reproduce

Running on Windows.

```shell
sqlfluff lint .
```

sample:

```python
-- COMMAND ----------


CREATE WIDGET TEXT db DEFAULT "";
```

```python
# MAGIC %sql
# MAGIC
# MAGIC select
# MAGIC   'myid' as ID,
# MAGIC   *
# MAGIC from ${db}.mytable limit 1
```

### Dialect

sparksql

### Version

sqlfluff, version 0.13.0
Python 3.9.9

### Configuration


[tool.sqlfluff.core]
templater = "jinja"
sql_file_exts = ".sql,.sql.j2,.dml,.ddl"
dialect = "sparksql"
exclude_rules = "L001"

[tool.sqlfluff.indentation]
indented_joins = false
indented_using_on = true
template_blocks_indent = false

[tool.sqlfluff.templater]
unwrap_wrapped_queries = true

[tool.sqlfluff.templater.jinja]
apply_dbt_builtins = true

[tool.sqlfluff.rules]
tab_space_size = 4
max_line_length = 160
indent_unit = "space"
comma_style = "trailing"
allow_scalar = true
single_table_references = "consistent"
unquoted_identifiers_policy = "all"

### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)


Discussion:
tunetheweb: Added a fix for #3356 to this PR since in same area of code.

Patch:
diff --git a/docs/source/configuration.rst b/docs/source/configuration.rst
index 6ba478884..1f1339ff0 100644
--- a/docs/source/configuration.rst
+++ b/docs/source/configuration.rst
@@ -276,6 +276,10 @@ A few common styles are supported:
     -- colon
     WHERE bla = :my_name
 
+    -- colon_nospaces
+    -- (use with caution as more prone to false positives)
+    WHERE bla = table:my_name
+
     -- numeric_colon
     WHERE bla = :2
 
@@ -289,7 +293,7 @@ A few common styles are supported:
     WHERE bla = ?
 
     -- numeric_dollar
-    WHERE bla = $3
+    WHERE bla = $3 or WHERE bla = ${3}
 
     -- percent
     WHERE bla = %s
diff --git a/src/sqlfluff/core/templaters/placeholder.py b/src/sqlfluff/core/templaters/placeholder.py
index ee054f9a5..3fbce482f 100644
--- a/src/sqlfluff/core/templaters/placeholder.py
+++ b/src/sqlfluff/core/templaters/placeholder.py
@@ -21,6 +21,8 @@ templater_logger = logging.getLogger("sqlfluff.templater")
 KNOWN_STYLES = {
     # e.g. WHERE bla = :name
     "colon": regex.compile(r"(?<![:\w\x5c]):(?P<param_name>\w+)(?!:)", regex.UNICODE),
+    # e.g. WHERE bla = table:name - use with caution as more prone to false positives
+    "colon_nospaces": regex.compile(r":(?P<param_name>\w+)", regex.UNICODE),
     # e.g. WHERE bla = :2
     "numeric_colon": regex.compile(
         r"(?<![:\w\x5c]):(?P<param_name>\d+)", regex.UNICODE
@@ -29,8 +31,10 @@ KNOWN_STYLES = {
     "pyformat": regex.compile(
         r"(?<![:\w\x5c])%\((?P<param_name>[\w_]+)\)s", regex.UNICODE
     ),
-    # e.g. WHERE bla = $name
-    "dollar": regex.compile(r"(?<![:\w\x5c])\$(?P<param_name>[\w_]+)", regex.UNICODE),
+    # e.g. WHERE bla = $name or WHERE bla = ${name}
+    "dollar": regex.compile(
+        r"(?<![:\w\x5c])\${?(?P<param_name>[\w_]+)}?", regex.UNICODE
+    ),
     # e.g. WHERE bla = ?
     "question_mark": regex.compile(r"(?<![:\w\x5c])\?", regex.UNICODE),
     # e.g. WHERE bla = $3
@@ -146,7 +150,7 @@ class PlaceholderTemplater(RawTemplater):
                 param_name = found_param["param_name"]
             last_literal_length = span[0] - last_pos_raw
             try:
-                replacement = context[param_name]
+                replacement = str(context[param_name])
             except KeyError as err:
                 # TODO: Add a url here so people can get more help.
                 raise SQLTemplaterError(


Test patch:
diff --git a/test/core/templaters/placeholder_test.py b/test/core/templaters/placeholder_test.py
index 1549cfbaf..c57389e58 100644
--- a/test/core/templaters/placeholder_test.py
+++ b/test/core/templaters/placeholder_test.py
@@ -79,6 +79,20 @@ def test__templater_raw():
                 city_ids="(1, 2, 3, 45)",
             ),
         ),
+        (
+            """
+            SELECT user_mail, city_id
+            FROM users_data:table_suffix
+            """,
+            "colon_nospaces",
+            """
+            SELECT user_mail, city_id
+            FROM users_data42
+            """,
+            dict(
+                table_suffix="42",
+            ),
+        ),
         (
             """
             SELECT user_mail, city_id
@@ -123,6 +137,8 @@ def test__templater_raw():
             FROM users_data
             WHERE (city_id) IN %(city_id)s
             AND date > %(date)s
+            AND someflag = %(someflag)s
+            LIMIT %(limit)s
             """,
             "pyformat",
             """
@@ -130,10 +146,11 @@ def test__templater_raw():
             FROM users_data
             WHERE (city_id) IN (1, 2, 3, 45)
             AND date > '2020-10-01'
+            AND someflag = False
+            LIMIT 15
             """,
             dict(
-                city_id="(1, 2, 3, 45)",
-                date="'2020-10-01'",
+                city_id="(1, 2, 3, 45)", date="'2020-10-01'", limit=15, someflag=False
             ),
         ),
         (
@@ -142,6 +159,7 @@ def test__templater_raw():
             FROM users_data
             WHERE (city_id) IN $city_id
             AND date > $date
+            OR date = ${date}
             """,
             "dollar",
             """
@@ -149,6 +167,7 @@ def test__templater_raw():
             FROM users_data
             WHERE (city_id) IN (1, 2, 3, 45)
             AND date > '2020-10-01'
+            OR date = '2020-10-01'
             """,
             dict(
                 city_id="(1, 2, 3, 45)",
@@ -221,6 +240,7 @@ def test__templater_raw():
         "colon_simple_substitution",
         "colon_accept_block_at_end",
         "colon_tuple_substitution",
+        "colon_nospaces",
         "question_mark",
         "numeric_colon",
         "pyformat",