如何大写字符串? [英] How to capitalize a string?

问题描述

我需要大写一个字符串:

I need to capitalize a string:

john doe-> John Doe

john doe -> John Doe

如何执行此操作?

我想需要使用NORMALIZE_AND_CASEFOLD，但是它返回一个小写字母.

I suppose need to use NORMALIZE_AND_CASEFOLD, but it returns a lower case.

https://cloud.google .com/bigquery/docs/reference/standard-sql/functions-and-operators#normalize_and_casefold

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(值[，标准化模式]) 说明

NORMALIZE_AND_CASEFOLD(value[, normalization_mode]) Description

获取STRING，值并执行与NORMALIZE相同的操作，并对不区分大小写的操作进行大小写折叠.

Takes a STRING, value, and performs the same actions as NORMALIZE, as well as casefolding for case-insensitive operations.

NORMALIZE_AND_CASEFOLD支持四种可选的规范化模式:

NORMALIZE_AND_CASEFOLD supports four optional normalization modes:

值名称说明 NFC规范化表格规范组成通过规范对等分解和重构字符. NFKC规范化形式兼容性构成通过兼容性分解字符，然后通过规范对等重新组合字符. NFD规范化形式规范分解通过规范等价分解字符，并以特定顺序排列多个组合字符. NFKD规范化形式兼容性分解通过兼容性分解字符，并按特定顺序排列多个组合字符.

Value Name Description NFC Normalization Form Canonical Composition Decomposes and recomposes characters by canonical equivalence. NFKC Normalization Form Compatibility Composition Decomposes characters by compatibility, then recomposes them by canonical equivalence. NFD Normalization Form Canonical Decomposition Decomposes characters by canonical equivalence, and multiple combining characters are arranged in a specific order. NFKD Normalization Form Compatibility Decomposition Decomposes characters by compatibility, and multiple combining characters are arranged in a specific order.

推荐答案

以下内容适用于BigQuery标准SQL，并使用JS UDF(第一个查询)和SQL UDF(第二个查询)

Below is for BigQuery Standard SQL and with use of JS UDF (first query) and SQL UDF (second one)

#standardSQL
CREATE TEMPORARY FUNCTION capitalize(str STRING)
RETURNS STRING
LANGUAGE js AS """
  return str.replace(
      /\\w\\S*/g,
      function(txt) {
          return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
      }
  );
""";
SELECT str, capitalize(str) capitalized_str
FROM `project.dataste.table`  

或

#standardSQL
CREATE TEMPORARY FUNCTION capitalize(str STRING) AS ((
  SELECT STRING_AGG(CONCAT(UPPER(SUBSTR(word, 1, 1)), LOWER(SUBSTR(word, 2))), ' ' ORDER BY pos)
  FROM UNNEST(SPLIT(str, ' ')) word WITH OFFSET pos
));
SELECT str, capitalize(str) capitalized_str
FROM `project.dataste.table`

您可以使用

WITH `project.dataste.table` AS (
  SELECT 'john doe' AS str UNION ALL
  SELECT 'abc xyz'
)

两个选项都具有结果:

Row str         capitalized_str  
1   john doe    John Doe     
2   abc xyz     Abc Xyz    

为JS UDF添加了最少的详细信息/更加简化的版本

Added least verbose / more streamlined version for JS UDF

#standardSQL
CREATE TEMPORARY FUNCTION capitalize(str STRING)
RETURNS STRING
LANGUAGE js AS """
  return str.replace(/\\b(.)/g, function(match, chr){return chr.toUpperCase();});
""";
SELECT str, capitalize(str) capitalized_str
FROM `project.dataste.table` 

这篇关于如何大写字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！