SQLServer 文字列関数一覧

SQLServerで文字列を操作する際に使用する主なSQL関数です。

文字列から一部の文字列を抜き出したい。
文字列を置換したい。
文字列の書式を指定して変換したい。
大文字、小文字を変換したい。
文字列を連結したい。
特定の文字を繰り返した文字列を生成したい。

などなど…
文字列を操作する関数を活用することで、データを取得した後にプログラム側で加工しなくても、SQL側で要件・仕様に合う文字列を生成することができます。

SQLServerの文字列を操作する主な関数

関数名	概要
ASCII	文字列の一番左の文字（先頭の文字）のASCIIコード値を返す。
CHAR	ASCIIコード値を文字値に変換して返す。
CHARINDEX	指定した文字列が見つかった最初のインデックス（開始位置）を返す。
CONCAT	複数の文字列を連結した結果の文字列を返す。
CONCAT_WS	指定した区切り文字で複数の文字列を連結した結果の文字列を返す。
DIFFERENCE	SOUNDEX異なる2つの文字列のSOUNDEX（サウンデックス）値の差を整数値で返す。
FORMAT	指定した書式に変換した文字列を返す。
LEFT	文字列の左端から指定された文字数の文字列を抜き出して返す。
LEN	文字列の末尾の空白（半角スペース）を除いた文字数を返す。
LOWER	文字列を小文字に変換した値返す。
LTRIM	文字列の左端（先頭）から空白（半角スペース）を削除した値を返す。
NCHAR	指定した整数コードのUnicode文字を返す。
PATINDEX	指定したパターンが見つかった最初のインデックス（開始位置）を返す。
QUOTENAME	指定した文字列から区切り記号で囲まれた有効なSQL Server識別子に変換した値を返す。
REPLACE	指定した文字列を別の文字列に置き換えた値を返す。
REPLICATE	指定した文字列を指定した回数繰り返した値を返す。
REVERSE	文字列を反転（前後逆に）した値を返す。
RIGHT	文字列の右端から指定された文字数の文字列を抜き出して返す。
RTRIM	文字列の右端（末尾）から空白（半角スペース）を削除した値を返す。
SOUNDEX	2つの文字列の類似性を評価する4文字のコード（SOUNDEX）を返す。
SPACE	空白（半角スペース）を指定した回数繰り返した値を返す。
STR	数値を文字に変換した値を返す。
STRING_AGG	指定した区切り記号で文字列式の値を連結した値を返す。
STRING_ESCAPE	文字列の特殊文字をエスケープした値を返す。
STUFF	文字列の指定した位置にある文字列を指定した文字列で置き換えた値を返す。
SUBSTRING	文字列の指定した位置から指定した文字数の文字列を抜き出して返す。
TRIM	文字列の左端と右端（先頭と末尾）にある空白（半角スペース）、または指定した文字を削除した値を返す。
UNICODE	文字列の左端（先頭）にある文字をUnicode値に変換した値を返す。
UPPER	文字列を大文字に変換した値返す。

1 文字列を検索・置換する関数
2 文字列を連結・結合または分割する関数
3 文字列を連結・結合する関数
4 SOUNDEX（サウンデックス）関数
- 4.1 SOUNDEX値を取得する SOUNDEX
- 4.2 SOUNDEX値の差を取得する DIFFERENCE
5 文字列の一部を抽出して切り出す関数
6 文字列の文字数を取得する関数
- 6.1 文字数を取得する LEN
7 文字列を変換する関数
8 不要な文字を削除（トリミング）する関数
9 文字列を繰り返す関数
- 9.1 指定した文字を繰り返す REPLICATE
- 9.2 空白を繰り返す SPACE

文字列を検索・置換する関数

文字列が見つかった最初の位置を取得する CHARINDEX

文字列を検索したい時に使います。
CHARINDEX関数は文字列が見つかった場合は最初の位置（文字列の先頭のインデックス）を返します。見つからなかった場合は0を返します。

SELECT CHARINDEX('him', 'Say hello to him for me.');

1	SELECT CHARINDEX('him', 'Say hello to him for me.');

実行結果

CHARINDEX関数は検索を開始する位置を指定することもできます。

SELECT CHARINDEX('him', 'Say hello to him for me.', 15);

1	SELECT CHARINDEX('him', 'Say hello to him for me.', 15);

実行結果

上記の例では15文字目から検索しているため、「him」は見つからないので0が返ります。

パターンが見つかった最初の位置を取得する PATINDEX

パターンで文字列を検索したい時に使います。
PATINDEX関数はパターンが見つかった場合は最初の位置（文字列の先頭のインデックス）を返します。見つからなかった場合は0を返します。

SELECT PATINDEX('%[0-9]%', 'var i = (3 + 5) * 2');

1	SELECT PATINDEX('%[0-9]%', 'var i = (3 + 5) * 2');

実行結果

文字列を置換する REPLACE

文字列を置き換えたい時に使います。

SELECT REPLACE('Ｗ大学 経済学部 経済学科', '経済', '人文');

1	SELECT REPLACE('Ｗ大学経済学部経済学科', '経済', '人文');

実行結果

Ｗ大学人文学部人文学科

指定した位置にある文字列を置換する STUFF

指定した位置にある文字列と別の文字列を入れ替えたい時に使います。

SELECT STUFF('abcdefghijklmn', 5, 6, 'XYZ');

1	SELECT STUFF('abcdefghijklmn', 5, 6, 'XYZ');

実行結果

abcdXYZklmn

STUFF関数の2つ目のパラメーター（引数）に入れ替える元の文字列の開始位置を指定し、3つ目のパラメーター（引数）に文字数を指定します。
上記の例では5文字目の「e」から「j」までの6文字を「XYZ」と入れ替えています。

文字列を連結・結合する関数

複数の文字列を連結する CONCAT

文字列を連結したい時に使います。

SELECT CONCAT('abc', 'def', 'ghi');

1	SELECT CONCAT('abc', 'def', 'ghi');

実行結果

abcdefghi

指定した区切り文字で複数の文字列を連結する CONCAT_WS

区切り文字を指定して文字列を連結したい時に使います。

SELECT CONCAT_WS('_', 'abc', 'def', 'ghi', 'jkl');

1	SELECT CONCAT_WS('_', 'abc', 'def', 'ghi', 'jkl');

実行結果

abc_def_ghi_jkl

指定した区切り記号で文字列式の値を連結する STRING_AGG

区切り記号を指定して文字列式の値を連結したい時に使います。

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID int NOT NULL PRIMARY KEY,
    CUSTOMER_NAME nvarchar(20)
);
INSERT INTO CUSTOMER
VALUES
    (101, 'A'),
    (102, 'C'),
    (103, 'B');

SELECT STRING_AGG(CUSTOMER_NAME, ', ') FROM CUSTOMER;

CREATE TABLE CUSTOMER

(

CUSTOMER_ID int NOT NULL PRIMARY KEY,

CUSTOMER_NAME nvarchar(20)

);

INSERT INTO CUSTOMER

VALUES

(101, 'A'),

(102, 'C'),

(103, 'B');

SELECT STRING_AGG(CUSTOMER_NAME, ', ') FROM CUSTOMER;

実行結果

A, C, B

STRING_AGG関数は、文字列の末尾には区切り記号を追加しません。

必要に応じて、WITHIN GROUP句を使用することで、連結結果の順序を指定できます。

SELECT
    STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME )
    -- または
    -- STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME  ASC )
FROM
    CUSTOMER;

SELECT
    STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME  DESC )
FROM
    CUSTOMER;

SELECT

STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME )

-- または

-- STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME ASC )

FROM

CUSTOMER;

SELECT

STRING_AGG(CUSTOMER_NAME, ', ') WITHIN GROUP ( ORDER BY CUSTOMER_NAME DESC )

FROM

CUSTOMER;

実行結果

A, B, C
C, B, A

SOUNDEX（サウンデックス）関数

SOUNDEX値を取得する SOUNDEX

SOUNDEX値が取得したい時に使います。

SELECT 
    SOUNDEX('aloud'),
    SOUNDEX('allowed'),
    SOUNDEX('around');

SELECT

SOUNDEX('aloud'),

SOUNDEX('allowed'),

SOUNDEX('around');

実行結果

No	値
1	A120
2	A120
3	A126

SOUNDEX値の差を取得する DIFFERENCE

SOUNDEX値の差が取得したい時に使います。

SELECT
    DIFFERENCE('aloud', 'allowed'),
    DIFFERENCE('aloud', 'around'),
    DIFFERENCE('aloud', 'common'),
    DIFFERENCE('aloud', 'beautiful'),
    DIFFERENCE('aloud', 'yesterday');

SELECT

DIFFERENCE('aloud', 'allowed'),

DIFFERENCE('aloud', 'around'),

DIFFERENCE('aloud', 'common'),

DIFFERENCE('aloud', 'beautiful'),

DIFFERENCE('aloud', 'yesterday');

実行結果

No	値
1	4
2	3
3	2
4	1
5	0

文字列の一部を抽出して切り出す関数

左端から指定文字数を切り出す LEFT

文字列の左にある文字列を切り出したい時に使います。

SELECT LEFT('abcdefg', 5);

1	SELECT LEFT('abcdefg', 5);

実行結果

abcde

上記の例では左から5文字を切り出しています。

右端から指定文字数を切り出す RIGHT

文字列の右にある文字列を切り出したい時に使います。

SELECT RIGHT('opqrstuvwxyz', 7);

1	SELECT RIGHT('opqrstuvwxyz', 7);

実行結果

tuvwxyz

上記の例では右から7文字を切り出しています。

指定位置から指定文字数を切り出す SUBSTRING

文字列の指定位置にある文字列を切り出したい時に使います。

SELECT SUBSTRING('abcdefghijklmn', 3, 5);

1	SELECT SUBSTRING('abcdefghijklmn', 3, 5);

実行結果

cdefg

上記の例では3文字目から5文字を切り出しています。

文字列の文字数を取得する関数

文字数を取得する LEN

文字数が知りたい時に使います。

SELECT LEN('0123456789');

1	SELECT LEN('0123456789');

実行結果

文字列を変換する関数

先頭の文字をASCIIコードに変換する ASCII

ASCIIコードが知りたい時に使います。

SELECT ASCII('A');

1	SELECT ASCII('A');

実行結果

ASCIIコードを文字に変換する CHAR

ASCIIコードが表す文字が知りたい時に使います。

SELECT CHAR(90);

1	SELECT CHAR(90);

実行結果

指定した書式にフォーマットする FORMAT

値をフォーマットしたい時に使います。
フォーマットする書式には.NET Frameworkの書式指定文字列を使用します。

SELECT
    FORMAT(GETDATE(), 'D', 'en-US' ),
    FORMAT(123456, '#,###'),
    FORMAT(GETDATE(), 'yyyy/MM/dd');

SELECT

FORMAT(GETDATE(), 'D', 'en-US' ),

FORMAT(123456, '#,###'),

FORMAT(GETDATE(), 'yyyy/MM/dd');

実行結果

No	値
1	Friday, September 15, 2017
2	123,456
3	2017/09/15

小文字に変換する LOWER

小文字にしたい時に使います。

SELECT LOWER('LOWER CASE OR UPPER CASE');

1	SELECT LOWER('LOWER CASE OR UPPER CASE');

実行結果

lower case or upper case

大文字に変換する UPPER

大文字にしたい時に使います。

SELECT UPPER('lower case or upper case');

1	SELECT UPPER('lower case or upper case');

実行結果

LOWER CASE OR UPPER CASE

区切り記号で囲まれた有効なSQL Server識別子に変換する QUOTENAME

SQL Serverが識別できる形式の値が取得したい時に使います。

SELECT QUOTENAME('abc[]def');

1	SELECT QUOTENAME('abc[]def');

実行結果

[abc[]]def]

逆順に並べた文字列に変換する REVERSE

文字の順番を逆転したい時に使います。

SELECT REVERSE('zyx...cba');

1	SELECT REVERSE('zyx...cba');

実行結果

abc…xyz

数値を文字に変換する STR

数値を文字に変換したい時に使います。

SELECT
    STR('1234567890'),
    STR('12345678.9'),
    STR('1234567');

SELECT

STR('1234567890'),

STR('12345678.9'),

STR('1234567');

実行結果

No	値
1	1234567890
2	12345679
3	1234567

STR関数はデフォルト（既定値）で10桁の文字列に変換します。
変換の際、小数以下の値は丸められます。
桁数に満たない場合は左に空白（半角スペース）が埋められます。

STR関数には、桁数を指定することもできます。

SELECT
    STR('1234567890.56789', 15, 4),
    STR('1234567890.56789', 15),
    STR('1234567890.56789');

SELECT

STR('1234567890.56789', 15, 4),

STR('1234567890.56789', 15),

STR('1234567890.56789');

実行結果

No	値
1	1234567890.5679
2	1234567891
3	1234567891

特殊文字をエスケープする STRING_ESCAPE

エスケープ後の文字列が取得したい時に使います。

SELECT STRING_ESCAPE('\	/	' + CHAR(13) + '\\	"	', 'json');

1	SELECT STRING_ESCAPE('\ / ' + CHAR(13) + '\\ " ', 'json');

実行結果

\\\t\/\t\r\\\\\t\”\t

Unicode整数値を文字に変換する NCHAR

Unicodeが表す文字が知りたい時に使います。

SELECT NCHAR(65);

1	SELECT NCHAR(65);

実行結果

先頭の文字をUnicode整数値に変換する UNICODE

Unicodeが知りたい時に使います。

SELECT UNICODE('Z');

1	SELECT UNICODE('Z');

実行結果

不要な文字を削除（トリミング）する関数

左端から空白を削除する LTRIM

文字列の左から半角スペースを取り除きたい時に使います。

SELECT LTRIM('  abc def  ');

1	SELECT LTRIM(' abc def ');

実行結果

abc def

右端から空白を削除する RTRIM

文字列の右から半角スペースを取り除きたい時に使います。

SELECT RTRIM('  abc def  ');

1	SELECT RTRIM(' abc def ');

実行結果

abc def

左右から空白または指定した文字を削除する TRIM

文字列の左右から文字をを取り除きたい時に使います。

SELECT TRIM('  abc def  ');

1	SELECT TRIM(' abc def ');

実行結果

abc def

TRIM関数はパラメーター（引数）に文字を指定することで、空白（半角スペース）以外の文字を取り除くこともできます。
取り除く文字を指定する場合はFROM句を使用します。

SELECT TRIM('#\$ ,.!' FROM '  , #    \ terget ! $  .');

1	SELECT TRIM('#\$ ,.!' FROM ' , # \ terget ! $ .');

実行結果

terget

文字列を繰り返す関数

指定した文字を繰り返す REPLICATE

文字列を繰り返した値が取得したい時に使います。

SELECT REPLICATE('-*-', 8);

1	SELECT REPLICATE('-*-', 8);

実行結果

-*–*–*–*–*–*–*–*-

空白を繰り返す SPACE

半角スペースを繰り返した値が取得したい時に使います。

SELECT SPACE(3);

1	SELECT SPACE(3);

実行結果

※半角スペースが3つ表示されています。

参考URL
文字列関数 (Transact-SQL)

SQL Server の他の関数一覧については、以下の記事を参照してください。