Microsoft SQL Server
文字列関数

備考

左

文字列の最も左の文字から始まり、指定された最大長までのサブ文字列を返します。

パラメーター：

1. 文字式。文字式は、 textまたはntextを除き、暗黙的にvarcharまたはnvarchar変換できる任意のデータ型にすることができます
2. 最大長。 0からbigint最大値までの整数（9,223,372,036,854,775,807）。
   max lengthパラメータが負の場合、エラーが発生します。

SELECT LEFT('This is my string', 4) -- result: 'This'

最大長が文字列の文字数より大きい場合、エンティティ文字列が返されます。

SELECT LEFT('This is my string', 50) -- result: 'This is my string'

右

指定された最大長を持つ、文字列の右端の部分文字列を返します。

パラメーター：

1. 文字式。文字式は、 textまたはntextを除き、暗黙的にvarcharまたはnvarchar変換できる任意のデータ型にすることができます
2. 最大長。 0からbigint最大値までの整数（9,223,372,036,854,775,807）。 max lengthパラメータが負の場合、エラーが発生します。

SELECT RIGHT('This is my string', 6) -- returns 'string'

最大長が文字列の文字数より大きい場合、エンティティ文字列が返されます。

SELECT RIGHT('This is my string', 50) -- returns 'This is my string'

部分文字列

指定された開始インデックスと指定された最大長にあるcharで始まる部分文字列を返します。

パラメーター：

1. 文字式。文字式は、 textまたはntextを除き、暗黙的にvarcharまたはnvarchar変換できる任意のデータ型にすることができます。
2. インデックスを開始します。要求された部分文字列の開始インデックスを指定する数値（ intまたはbigint ）。 （ 注： SQL Serverの文字列は基本1インデックスであり、文字列の最初の文字はインデックス1です）。この場合、開始インデックスと最大長の合計が0より大きい場合、戻り文字列は文字式の最初の文字から始まり、長さが（start index +最大長 - 1）。 0より小さい場合、空の文字列が返されます。
3. 最大長0からbigint最大値までの整数（9,223,372,036,854,775,807）。 max lengthパラメータが負の場合、エラーが発生します。

SELECT SUBSTRING('This is my string', 6, 5) -- returns 'is my'

最大長+開始インデックスが文字列内の文字数より多い場合、エンティティ文字列が返されます。

SELECT SUBSTRING('Hello World',1,100) -- returns 'Hello World'

開始インデックスが文字列の文字数より大きい場合、空の文字列が返されます。

SELECT SUBSTRING('Hello World',15,10) -- returns ''

ASCII

文字列の左端の文字のASCIIコードを表すint値を返します。

SELECT ASCII('t') -- Returns 116
SELECT ASCII('T') -- Returns 84
SELECT ASCII('This') -- Returns 84

文字列がUnicodeで、左端の文字がASCIIではないが現在の照合で表現可能な場合は、127より大きい値が返されます。

SELECT ASCII(N'ï') -- returns 239 when `SERVERPROPERTY('COLLATION') = 'SQL_Latin1_General_CP1_CI_AS'`

文字列がUnicodeで、現在の照合で一番左の文字を表すことができない場合は、63のint値が返されます（ASCIIの疑問符を表します）。

SELECT ASCII(N'߷') -- returns 63
SELECT ASCII(nchar(2039)) -- returns 63

CharIndex

別の文字列式の中で最初に現れる文字列式の開始インデックスを返します。

パラメータリスト：

1. 検索する文字列（最大8000文字）
2. 検索する文字列（任意の有効な文字データ型と長さ、バイナリを含む）
3. （オプション）インデックスを開始します。 int型またはbig int型の数。省略または1未満の場合、文字列の先頭から検索が開始されます。

検索する文字列がvarchar(max) 、 nvarchar(max)またはvarbinary(max)場合、 CHARINDEX関数はbigint値を返します。それ以外の場合は、 intを返します。

SELECT CHARINDEX('is', 'this is my string') -- returns 3
SELECT CHARINDEX('is', 'this is my string', 4) -- returns 6
SELECT CHARINDEX(' is', 'this is my string') -- returns 5

チャー

int ASCIIコードで表されるcharを返します。

SELECT CHAR(116) -- Returns 't'
SELECT CHAR(84)  -- Returns 'T'

これは、新しい行/改行CHAR(10) 、改行CHAR(13)などを導入するために使用できます。参考までにAsciiTable.comを参照してください。

引数の値が0〜255でない場合、CHAR関数はNULL返しNULL 。
CHAR関数の戻りデータ型はchar(1)

レン

文字列の文字数を返します。
注： LEN関数は後続のスペースを無視します。

SELECT LEN('My string'), -- returns 9
       LEN('My string   '), -- returns 9
       LEN('   My string') -- returns 12

末尾のスペースを含む長さが望ましい場合は、これを達成するためのいくつかの手法がありますが、それぞれには欠点があります。 1つの方法は、文字列に1文字を追加して、 LENから1を引いたものを使用することです。

DECLARE @str varchar(100) = 'My string   '
SELECT LEN(@str + 'x') - 1 -- returns 12

欠点は、文字列変数または列の型が最大長であり、余分な文字の追加が破棄され、結果の長さが後続の空白をカウントしない場合です。これに対処するために、以下の修正バージョンは問題を解決し、少数の追加の実行時間を犠牲にしてすべてのケースで正しい結果をもたらします。これにより、サロゲートペアと合理的な実行速度を含む正しい結果が得られます。使用するのに最適なテクニックのようです：

SELECT LEN(CONVERT(NVARCHAR(MAX), @str) + 'x') - 1

もう1つの方法は、 DATALENGTH関数を使用するDATALENGTHです。

DECLARE @str varchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 12

ただし、 DATALENGTHはメモリー内の文字列の長さをバイト単位で戻しますが、注意が必要DATALENGTH 。これは、 varcharとnvarcharでは異なります。

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 24

これを調整するには、文字列のデータ長を1文字のデータ長（同じ型でなければならない）で除算します。次の例ではこれを行い、ターゲット文字列が空の場合も処理し、ゼロ除算を回避しています。

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) / DATALENGTH(LEFT(LEFT(@str, 1) + 'x', 1)) -- returns 12

ただし、これでもSQL Server 2012以降では問題があります。文字列にサロゲートペアが含まれていると、不正な結果が生成されます（一部の文字は同じ文字列の他の文字よりも多くのバイトを占めることがあります）。

もう1つの方法は、 REPLACEを使用して空白を空白以外の文字に変換し、結果のLENを取る方法です。これにより、すべてのケースで正しい結果が得られますが、長い文字列では実行速度が非常に悪くなります。

連合

SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'

SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
       'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.

SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'

DECLARE @Age INT=23;
SELECT CONCAT('Ram is ', @Age,' years old');  -- returns 'Ram is 23 years old'

SELECT 'This is the number ' + CAST(42 AS VARCHAR(5)) --returns 'This is the number 42'

下げる

すべての大文字を小文字に変換した後に、文字式（ varcharまたはnvarchar ）を返します。

パラメーター：

1. 文字式。暗黙的にvarchar変換できる文字またはバイナリデータの任意の式。

SELECT LOWER('This IS my STRING') -- Returns 'this is my string'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT LOWER(@String) -- Returns 'this is my string'

アッパー

すべての小文字を大文字に変換した後に文字式（ varcharまたはnvarchar ）を返します。

パラメーター：

1. 文字式。暗黙的にvarchar変換できる文字またはバイナリデータの任意の式。

SELECT UPPER('This IS my STRING') -- Returns 'THIS IS MY STRING'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT UPPER(@String) -- Returns 'THIS IS MY STRING'

LTrim

最初のすべての空白、つまり左から最初の空白以外の空白文字を削除した後に、文字式（ varcharまたはnvarchar ）を返します。

パラメーター：

1. 文字式。暗黙的にvarcher （ text 、 ntext 、 imageを除く）に変換できる文字またはバイナリデータの表現。

SELECT LTRIM('    This is my string') -- Returns 'This is my string'

RTrim

末尾の空白をすべて削除した後の文字式（ varcharまたはnvarchar ）を返します。空白は、文字列の右端から最初の空白文字がなくなるまで上書きされます。

パラメーター：

1. 文字式。暗黙的にvarcher （ text 、 ntext 、 imageを除く）に変換できる文字またはバイナリデータの表現。

SELECT RTRIM('This is my string     ') -- Returns 'This is my string'

Unicode

入力式の最初の文字のUnicode値を表す整数値を返します。

パラメーター：

1. Unicode文字表現。任意の有効なncharまたはnvarchar式。

SELECT UNICODE(N'Ɛ') -- Returns 400

DECLARE @Unicode nvarchar(11) = N'Ɛ is a char'
SELECT UNICODE(@Unicode) -- Returns 400

NChar

Unicode規格で定義されている整数引数に対応するUnicode文字（ nchar(1)またはnvarchar(2) ）を返します。

パラメーター：

1. 整数式。 0〜65535の正の整数式、またはデータベースの照合で補助文字（CS）フラグがサポートされている場合、サポートされる範囲は0〜1114111です。整数式がこの範囲内にない場合、 nullが戻ってきた。

SELECT NCHAR(257) -- Returns 'ā'
SELECT NCHAR(400) -- Returns 'Ɛ'

逆

逆の順序で文字列値を返します。

パラメーター：

1. 文字列式。暗黙的にvarchar変換できる任意の文字列またはバイナリデータ。

Select REVERSE('Sql Server') -- Returns 'revreS lqS'

PatIndex

指定された式で指定されたパターンが最初に出現する開始位置を返します。

パラメーター：

1. パターン。見つかったシーケンスを含む文字式。最大長は8000文字に制限されています。パターンにはワイルドカード（ % 、 _ ）を使用できます。パターンがワイルドカードで始まらない場合は、式の先頭にあるものにのみ一致することがあります。ワイルドカードで終わらない場合は、式の末尾にあるものにのみ一致します。

2. 表現。任意の文字列データ型。

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3. 

SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3. 

SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start. 

SELECT PATINDEX('inter%', 'interesting') -- Returns 1. 

SELECT PATINDEX('%ing', 'interesting') -- Returns 9. 

スペース

繰り返されるスペースの文字列（ varchar ）を返します。

パラメーター：

1. 整数式。整数式で、8000までです。負の場合はnullが戻されます。 0の場合、空の文字列が返されます。 （8000文字以上の文字列を返すには、Replicateを使います。

SELECT SPACE(-1) -- Returns NULL
SELECT SPACE(0)  -- Returns an empty string
SELECT SPACE(3)  -- Returns '   ' (a string containing 3 spaces)

複製する

指定された回数だけ文字列値を繰り返します。

パラメーター：

1. 文字列式。文字列式には、文字列またはバイナリデータを使用できます。
2. 整数式。 bigintを含む任意の整数型。負の場合、 nullが返されます。 0の場合、空の文字列が返されます。

SELECT REPLICATE('a', -1)  -- Returns NULL

SELECT REPLICATE('a', 0)  -- Returns ''

SELECT REPLICATE('a', 5)  -- Returns 'aaaaa'

SELECT REPLICATE('Abc', 3) -- Returns 'AbcAbcAbc'

注：文字列式の型がvarchar(max)またはnvarchar(max)でなければ、戻り値は8000文字を超えません。戻り値がその制限を超えてしまう文字列を追加する前に、レプリケートが停止します。

SELECT LEN(REPLICATE('a b c d e f g h i j k l', 350)) -- Returns 7981

SELECT LEN(REPLICATE(cast('a b c d e f g h i j k l' as varchar(max)), 350)) -- Returns 8050

置換

指定されたサブストリングのすべての出現が別のサブストリングに置き換えられているストリング（ varcharまたはnvarchar ）をnvarcharます。

パラメーター：

1. 文字列式。これは検索される文字列です。文字またはバイナリのデータ型にすることができます。
2. パターン。これは置き換えられるサブストリングです。文字またはバイナリのデータ型にすることができます。 pattern引数は空の文字列にすることはできません。
3. 置換。これは、パターンのサブストリングを置き換えるサブストリングです。これは、文字データまたはバイナリデータです。

SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.

ノート：

• 文字列式の型がvarchar(max)またはnvarchar(max)でなければ、 replace関数は8,000文字で戻り値を切り捨てます。
• 戻り値のデータ型は入力データ型に依存します。入力値のいずれかがnvarchar場合はnvarchar 、それ以外の場合はvarchar返します。
• いずれかの入力パラメータがNULL場合はNULL

String_Split

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

value
-----
Lorem
ipsum
dolor
sit
amet.

ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130

Str

数値データから変換された文字データ（ varchar ）を返します。

パラメーター：

1. 浮動式。小数点を含む近似数値データ型。
2. 長さオプション。数字、小数点、および先頭のスペース（必要な場合）を含む、返される文字列式の合計の長さ。デフォルト値は10です。
3. 小数。 オプション。小数点以下の桁数。 16より大きい場合、結果は小数点の右側の16桁に切り捨てられます。

SELECT STR(1.2) -- Returns '         1'

SELECT STR(1.2, 3) -- Returns '  1'

SELECT STR(1.2, 3, 2) -- Returns '1.2'

SELECT STR(1.2, 5, 2) -- Returns ' 1.20'

SELECT STR(1.2, 5, 5) -- Returns '1.200'

SELECT STR(1, 5, 2) -- Returns ' 1.00'

SELECT STR(1) -- Returns '         1'

クォートネーム

有効なSQL Serverで区切られた識別子にするために区切り文字で囲まれたUnicode文字列を返します。

パラメーター：

1. 文字列。 Unicodeデータの文字列。最大128文字（ sysname ）。入力文字列が128文字を超える場合、関数はnull返しnull 。
2. 引用符文字。 オプション 。区切り文字として使用する単一の文字。 （単一引用符であり得る'または``）、左または右のブラケット（ { 、 [ 、 ( 、 <または> 、 ) 、 ] 、 } ）または二重引用符（ " ）。他の値は、NULLを返しますデフォルト値は大括弧で囲まれています。

SELECT QUOTENAME('what''s my name?')      -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '[') -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', ']') -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '''') -- Returns 'what''s my name?'

SELECT QUOTENAME('what''s my name?', '"') -- Returns "what's my name?"

SELECT QUOTENAME('what''s my name?', ')') -- Returns (what's my name?)
SELECT QUOTENAME('what''s my name?', '(') -- Returns (what's my name?)

SELECT QUOTENAME('what''s my name?', '<') -- Returns <what's my name?>
SELECT QUOTENAME('what''s my name?', '>') -- Returns <what's my name?>

SELECT QUOTENAME('what''s my name?', '{') -- Returns {what's my name?}
SELECT QUOTENAME('what''s my name?', '}') -- Returns {what's my name?}

SELECT QUOTENAME('what''s my name?', '`') -- Returns `what's my name?`

Soundex

2つの文字列の音声類似度を評価する4文字コード（ varchar ）を返します。

パラメーター：

1. 文字式。文字データの英数字表現。

soundex関数は、話​​したときに文字式がどのように発音するかに基づいて4文字のコードを作成します。最初のcharはパラメータの最初の文字の大文字バージョン、残りの3文字は式の文字を表す数字です（無視されるa、e、i、o、u、h、w、yは除く） 。

SELECT SOUNDEX ('Smith') -- Returns 'S530'

SELECT SOUNDEX ('Smythe') -- Returns 'S530'

差

2つの文字式のsoundex値の差を示す整数（ int ）値を返します。

パラメーター：

1. 文字式1。
2. 文字式2。

両方のパラメーターは、文字データの英数字式です。

返される整数は、同じパラメータのsoundex値の文字数です。したがって、4は式が非常に似ていることを意味し、0は非常に異なることを意味します。

SELECT  SOUNDEX('Green'),  -- G650
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Green','Greene') -- Returns 4
        
SELECT  SOUNDEX('Blotchet-Halls'),  -- B432
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Blotchet-Halls', 'Greene') -- Returns 0

フォーマット

DECLARE @d DATETIME = '2016-07-31';  

SELECT 
    FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2016'
   ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2016'
   ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2016'
   ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2016/7/31'
   ,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Sunday, July 31, 2016'
   ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2016'
   ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Sonntag, 31. Juli 2016'

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2016'
      ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789',
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Sunday, July 31, 2016 12:00:00 AM'
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns रविवार, जुलाई 31, 2016 12:00:00 पूर्वाह्न
      ,FORMAT ( @d, 'dddd', 'en-US' )  AS 'US' -- Returns 'Sunday'
      ,FORMAT ( @d, 'dddd', 'hi-IN' )  AS 'Hindi' -- Returns 'रविवार'

DECLARE @Price1 INT = 40
SELECT FORMAT(@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'      
       ,FORMAT(@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default', -- Returns '$40.36'
       FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
       FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
       FORMAT( @Price, 'C2') AS 'With 2 Decimal', -- Returns '$40.36'

   DECLARE @Percentage float = 0.35674
   SELECT FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
   FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
   FORMAT( @Percentage, 'P1') AS '% with 1 Decimal'  -- Returns '35.7 %'

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA', -- Returns '4,54,545.39'
FORMAT( @Number, '#.0')     AS 'With 1 Decimal', -- Returns '454545.4'
FORMAT( @Number, '#.00')    AS 'With 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '000454545'

Category         Type             .Net type
-------------------------------------------
Numeric          bigint           Int64
Numeric          int              Int32
Numeric          smallint         Int16
Numeric          tinyint          Byte
Numeric          decimal          SqlDecimal
Numeric          numeric          SqlDecimal
Numeric          float            Double
Numeric          real             Single
Numeric          smallmoney       Decimal
Numeric          money            Decimal
Date and Time    date             DateTime
Date and Time    time             TimeSpan
Date and Time    datetime         DateTime
Date and Time    smalldatetime    DateTime
Date and Time    datetime2        DateTime
Date and Time    datetimeoffset   DateTimeOffset

String_エスケープ

SELECT STRING_ESCAPE('\   /  
\\    "     ', 'json') -- returns '\\\t\/\n\\\\\t\"\t'

Special character    Encoded sequence
-------------------------------------
Quotation mark (")   \"
Reverse solidus (\)  \\
Solidus (/)          \/
Backspace            \b
Form feed            \f
New line             \n
Carriage return      \r
Horizontal tab       \t


Control character    Encoded sequence
------------------------------------
CHAR(0)            \u0000
CHAR(1)            \u0001
...                ...
CHAR(31)           \u001f