FrankChen021 commented on code in PR #16862:
URL: https://github.com/apache/druid/pull/16862#discussion_r1713114204
##########
docs/querying/sql-functions.md:
##########
@@ -1341,28 +1427,90 @@ Returns the rank with gaps for a row within a window.
For example, if two rows t
## REGEXP_EXTRACT
-`REGEXP_EXTRACT(<CHARACTER>, <CHARACTER>, [<INTEGER>])`
+Apply regular expression `pattern` to `expr` and extract the `N`-th capture
group. If `N` is unspecified or zero, returns the first substring that matches
the pattern. Returns `null` if there is no matching pattern.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `REGEXP_EXTRACT(expr, pattern[, N])`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example uses regular expressions to find city names inside the
`OriginCityName` column from the `flight-carriers` datasource by matching what
comes before the comma.
+
+```sql
+SELECT
+ "OriginCityName" AS "origin_city",
+ REGEXP_EXTRACT("OriginCityName", '([^,]+)', 0) AS "pattern_match"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `origin_city` | `pattern_match` |
+| -- | -- |
+| `San Juan, PR` | `San Juan`|
+
+</details>
-Applies a regular expression to the string expression and returns the _n_th
match.
+[Learn more](sql-scalar.md#string-functions)
## REGEXP_LIKE
-`REGEXP_LIKE(<CHARACTER>, <CHARACTER>)`
+Returns `true` if the regular expression `pattern` finds a match in `expr`.
Returns `false` otherwise.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `REGEXP_LIKE(expr, pattern)`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example returns `true` when the `OriginCityName` column from
`flight-carriers` has a city name with a space in the name.
+
+```sql
+SELECT
+ "OriginCityName" AS "origin_city",
+ REGEXP_LIKE("OriginCityName", '[A-Za-z]+\s[A-Za-z]+') AS "pattern_found"
+FROM "flight-carriers"
+LIMIT 2
+```
-Returns true or false signifying whether the regular expression finds a match
in the string expression.
+Returns the following:
+
+| `origin_city` | `pattern_found` |
+| -- | -- |
+| `San Juan, PR` | `true` |
+| `Boston, MA` | `false` |
+
+</details>
+
+[Learn more](sql-scalar.md#string-functions)
## REGEXP_REPLACE
-`REGEXP_REPLACE(<CHARACTER>, <CHARACTER>, <CHARACTER>)`
+Replaces all occurrences of a regular expression in a string expression with a
replacement string. The replacement string may refer to capture groups using
`$1`, `$2`, etc.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `REGEXP_REPLACE(expr, pattern, replacement)`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example matches instances of the word `Fort` and replaces it
with it's abbreviation `Ft.`
+
+```sql
+SELECT
+ 'Fort Lauderdale, FL' AS "original_string",
+ REGEXP_REPLACE('Fort Lauderdale, FL', 'Fort', 'Ft.') AS "modified_string"
Review Comment:
can we also add an example of how capture group replacement(like $1) is used?
##########
docs/querying/sql-functions.md:
##########
@@ -1085,19 +1085,63 @@ Looks up the expression in a registered query-time
lookup table.
## LOWER
-`LOWER(expr)`
+Returns the expression in lowercase.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `LOWER(expr)`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example converts the `OriginCityName` column from the
`flight-carriers` datasource to lowercase.
+
+```sql
+SELECT
+ "OriginCityName" AS "origin_city",
+ LOWER("OriginCityName") AS "lowercase"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `origin_city` | `lowercase` |
+| -- | -- |
+`San Juan, PR` | `san juan, pr` |
+
+</details>
+
+[Learn more](sql-scalar.md#string-functions)
-Returns the expression in lowercase.
## LPAD
-`LPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])`
+Returns a string of size `length` from `expr`. When the length of `expr` is
less than `length`, left pads `expr` with `chars`, which defaults to the space
character. Truncates `expr` to `length` if `length` is shorter than the length
of `expr`.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `LPAD(expr, length[, chars])`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example left pads the value of `OriginState` from
`flight-carriers` to return a total of 5 characters.
+
+```sql
+SELECT
+ "OriginState" AS "origin_state",
+ LPAD("OriginState", 5, '+') AS "add_left_padding"
Review Comment:
can we also add a TRUNCATE example?
##########
docs/querying/sql-functions.md:
##########
@@ -1414,11 +1562,32 @@ Returns the number of the row within the window
starting from 1.
## RPAD
-`RPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])`
+Returns a string of size `length` from `expr`. When the length of `expr` is
less than `length`, right pads `expr` with `chars`, which defaults to the space
character. Truncates `expr` to `length` if `length` is shorter than the length
of `expr`.
-**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+* **Syntax:** `RPAD(expr, length[, chars])`
+* **Function type:** Scalar, string
+
+<details><summary>Example</summary>
+
+The following example inserts `+` characters as padding to the right of the
`OriginState` column from the `flight-carriers` datasource, making the return
expression a length of `5` characters.
+
+```sql
+SELECT
+ "OriginState" AS "origin_state",
+ RPAD("OriginState", 5, '+') AS "add_right_padding"
Review Comment:
can we also add a TRUNCATE example?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
