Databricks SQL にて 2 つ以上の文字にて TRIM する方法

## 概要

Databricks SQL にて 2 つ以上の文字にて TRIM する方法を共有します。半角スペースと全角スペースを TRIM する方法を提示しますが、Java の正規表現の箇所（`[^ 　]+`）を修正していただくことで応用できます。

## 実行コード

### 1. 半角スペースと全角スペースを TRIM する方法

```sql
-- TRIM 相当
CASE
  WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
    substring(
      col_name,
      (regexp_instr(col_name, '[^ 　]+')),
      len(col_name) - (regexp_instr(col_name, '[^ 　]+') - 1) - (regexp_instr(reverse(col_name), '[^ 　]+') - 1)
    )
  WHEN col_name IS NULL THEN
    NULL
  ELSE ''
END AS trim
```

### 2. 半角スペースと全角スペースを RTRIM する方法

```sql
CASE
  WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
    substring(
      col_name,
      1,
      len(col_name) - (regexp_instr(reverse(col_name), '[^ 　]+') - 1)
    )
  WHEN col_name IS NULL THEN
    NULL
  ELSE ''
END AS rtrim
```

### 3. 半角スペースと全角スペースを LTRIM する方法

```sql
CASE
  WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
    substring(
      col_name,
      (regexp_instr(col_name, '[^ 　]+')),
      len(col_name)
    )
  WHEN col_name IS NULL THEN
    NULL
  ELSE ''
END AS ltrim
```

## 実行例

### 1. 実行例①

```sql
df = spark.sql("""
WITH src AS (
  SELECT
    ' a 　 ' AS col_name
  UNION ALL
  SELECT
    ' 　 abc あいう 　  　  ' AS col_name
  UNION ALL
  SELECT
    ' ' AS col_name
  UNION ALL
  SELECT
    ' 　 ' AS col_name
  UNION ALL
  SELECT
    '' AS col_name
  UNION ALL
  SELECT
    NULL AS col_name
)
SELECT
  col_name
  -- TRIM 相当
  ,CASE
      WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
        substring(
          col_name,
          (regexp_instr(col_name, '[^ 　]+')),
          len(col_name) - (regexp_instr(col_name, '[^ 　]+') - 1) - (regexp_instr(reverse(col_name), '[^ 　]+') - 1)
        )
      WHEN col_name IS NULL THEN
        NULL
      ELSE ''
  END AS trim
  
  -- RTRIM 相当
  ,CASE
      WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
        substring(
          col_name,
          1,
          len(col_name) - (regexp_instr(reverse(col_name), '[^ 　]+') - 1)
        )
      WHEN col_name IS NULL THEN
        NULL
      ELSE ''
  END AS rtrim
  
  -- LTRIM 相当
  ,CASE
      WHEN (regexp_instr(col_name, '[^ 　]+')) > 0 THEN
        substring(
          col_name,
          (regexp_instr(col_name, '[^ 　]+')),
          len(col_name)
        )
      WHEN col_name IS NULL THEN
        NULL
      ELSE ''
  END AS ltrim
FROM
  src
""")
df.display()
df.collect()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b0cd2bb7-994c-4bf8-6b3f-2d3aa15bf6e3.png)

出力結果（`\u3000`が全角スペース）

```python
[
    Row(
        col_name=" a \u3000 ",
        trim="a",
        rtrim=" a",
        ltrim="a \u3000 ",
    ),
    Row(
        col_name=" \u3000 abc あいう \u3000  \u3000  ",
        trim="abc あいう",
        rtrim=" \u3000 abc あいう",
        ltrim="abc あいう \u3000  \u3000  ",
    ),
    Row(
        col_name=" ",
        trim="",
        rtrim="",
        ltrim="",
    ),
    Row(
        col_name=" \u3000 ",
        trim="",
        rtrim="",
        ltrim="",
    ),
    Row(
        col_name="",
        trim="",
        rtrim="",
        ltrim="",
    ),
    Row(
        col_name=None,
        trim=None,
        rtrim=None,
        ltrim=None,
    ),
]
```

## 更新情報

### 2024年2月1日 半角スペースと全角スペースのみの場合に空白になるように変更。

以下の出力結果が 0 の場合には、空白となるように変更。

```python
(regexp_instr(col_name, '[^ 　]+'))
```

### 2024年2月1日 NULL の場合に NULL になるように変更