데이터를 여러 형태로 바꾸다 보면 문자열을 조작해야 하는 경우가 많으므로 빅쿼리는 내장 문자열 함수 라이브러리를 제공한다.
WITH
example AS(
SELECT
*
FROM
UNNEST ( ['Seatle','New York', 'Singapore'] ) AS city )
SELECT
city,
LENGTH(city) AS len,
LOWER(city) AS lower,
STRPOS(city, 'or') AS orpos
FROM
example
이 쿼리는 문자열의 길이를 계산해 문자열을 소문자로 만든 후 'city' 컬럼에서 부분 문자열의 위치를 찾는다.
부분 문자열 'or'은 'new york' 및 'Singapore'에서 찾을 수 있지만 'Seattle'에서는 찾을 수 없다.
문자열 조작에 특히 유용한 두 가지 기능은 SUBSTR 및 CONCAT 이다. SUBSTR은 부분 문자열을 추출하고 CONCAT는 입력값을 연결한다.
WITH example AS (
SELECT 'armin@abc.com' AS email, 'Annapolis, MD' AS city
UNION ALL SELECT 'boyan@bca.com', 'Boulder, CD'
UNION ALL SELECT 'carrie@cab.com', 'Chicago, IL'
)
SELECT
CONCAT(
SUBSTR(email, 1, STRPOS(email,'@') -1), --username
' from ', city) AS callers
FROM example
■ 국제화
빅쿼리의 문자열은 유니코드이므로 영어와 관계된 가정은 피해야 한다. 예를 들어 일본어에는 '대문자'라는 것이 없으며, 문자열 타입을 바이트 배열 타입으로 변환할 때 기본적으로 적용되는 UTF-8 인코딩은 타밀어 같은 언어를 제대로 표현하지 못한다.
WITH
example AS(
SELECT
*
FROM
UNNEST( [ 'Seattle', 'New York', '東京' ] ) AS city )
SELECT
city,
UPPER(city) AS allcaps,
CAST(city AS BYTES ) AS bytes
FROM
example
내 컴퓨터에 타밀어는 없으므로 패스.
빅쿼리는 유니코드 문자 배열, 바이트 배열 및 유니코드 코드 포인트 배열(int 64)로 문자열을 나타내는 3가지 방법을 지원한다.
동일한 문자열에서 CHAR_LENGTH 및 BYTE_LENGTH 결과와 코드 포인트 수가 문자 수와 어떻게 다른지 확인하자.
WITH
example AS(
SELECT *
FROM
UNNEST( [ 'Seattle','New York', '東京' ] ) AS city )
SELECT
city,
CHAR_LENGTH(city) AS char_len,
to_code_points(city)[ORDINAL(1)] AS first_codept ,
ARRAY_LENGTH(to_code_points(city)) AS num_codept,
CAST (city AS bytes) AS bytes,
BYTE_LENGTH(city) AS byte_len
FROM
example
따라서 어떤 열에 다른 언어로 된 텍스트가 포함되어 있는지 파악한 후 언어의 차이를 고려해서 문자열 조작 함수를 사용해야 한다.
■ 출력 및 파싱
문자열을 파싱할 때는 간단히 INT64나 FLOAT64 타입으로 변환하면 되지만, 어떤 값을 원하는 형태의 문자열로 표현하라면 FORMAT 함수를 사용해야 한다.
SELECT
CAST(42 AS STRING),
CAST ('42' AS INT64),
FORMAT ('%03d',42),
FORMAT('%6.3f', 32.457842),
FORMAT('%5.3f', 32.4),
FORMAT('**%s**','H'),
FORMAT('%s-%03d','Agent',7)
FORMAT은 C의 printf 함수와 유사하게 작동하며 같은 형식 지정자를 사용한다. 좀 더 유용한 지정자 중 몇몇은 앞의 예에서 설명했다.
FORMAT 함수는 날짜나 타임스탬프 값도 지원하지만, 사용자의 로케일을 감안해서 날짜를 형식화할 수 있는 FORMAT_DATE와 FORMAT_TIMESTAMP 함수를 사용하는 것이 더 좋다.
■ 문자열 조작 함수
문자열 조작은 ETL 파이프라인에서 일반적으로 필요하므로, 다음 편의 함수를 잘 기억해 두면 유용한다.
