SQL Master

SQL ServerではDATEADD関数を使って、日付型の値に日時を加算する事ができます。


構文：

DATEADD(element, value, target)

targetにvalueで指定した値を加算する。elementにvalueの日付要素を記述する。日付要素は以下のようなものがある。
year(yy or yyyy) : 年
quarter(qq or q) : 四半期
month(mm or m) : 月
dayofyear(dy or y) : 年始からの日数
day(dd, d) : 日
week(wk, w) : 週
weekday(dw) : 曜日
hour(hh) : 時
minute(mi or n) : 分
second(ss) : 秒
millisecond(ms, s) : ミリ秒

使用例：birthday列の値の20日後の日付を求める。

SELECT birthday, DATEADD(day, 20, birthday) FROM tbEmpTable;

birthday 　　　　　　　　　　　　　　DATEADD(day, 20, birthday)
————————————————————
1970-08-01 00:00:00.000 　　　1970-08-21 00:00:00.000

使用例：birthday列の値に14時間を加算する。

SELECT birthday, DATEADD(hour, 14, birthday) FROM tbEmpTable;

birthday 　　　　　　　　　　　　　　DATEADD(hour, 14, birthday)
———————————————————–
1970-08-01 00:00:00.000 　　　1970-08-01 14:00:00.000

使用例：birthday列の値に2週間分の日付を加算する。

SELECT birthday, DATEADD(week, 2, birthday) FROM tbTestTable;

birthday　　　　　　　　　　　　　　DATEADD(week, 2, birthday)
———————————————————-
1970-08-01 14:00:00.000 　　　1970-08-15 14:00:00.000

SQLコマンド辞典に戻る

OracleやSQL Serverでは、CURRENT_TIMESTAMP関数を使って現在の日時を求める事ができます。


使用例：現在の日時を取得する。

SELECT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP
————————————–
2009-09-07 20:42:02.000

SQLコマンド辞典に戻る

Oracleでは、TO_CHAR関数を使って数値型や日付型の値を文字列型の値に変換する事ができます。


文法：

TO_CHAR(value [ , format [ , parameter ] ] )

valueで指定した値を文字列型に変換します。formatにOracleの書式モデルを指定することもできる。またparameterにはNSLパラメータを指定する事もでき、グループ区切り文字や、ローカル通貨文字、月や日の表記方法などの設定をする事ができる。

使用例：日付を文字列に変換する。

SELECT TO_CHAR(2009/09/03 13:00:00) FROM dual;

TO_CHAR(2009/09/03 13:00:00)
—————————————————-
09-09-03 13:00:00

SQLコマンド辞典に戻る

OracleやSQL Serverで値の型の変換を行うには、CAST関数を使います。


文法：

CAST( value AS type)

valueをtypeで指定した型に変換する。

使用例：数値を文字列に変換する。

SELECT CAST(0.66 AS varchar);

CAST(0.66 AS varchar)
———————————————
0.66

使用例：文字列を日付に変換する。

SELECT CAST(’09/09/11′ AS datetime);

CAST(’09/09/11′ AS datetime)
———————————————
2009-09-11 00:00:00.000

SQLコマンド辞典に戻る

Unicodeを文字列に変換するには、OracleではNCHR関数、SQL ServerではNCHAR関数を使います。


構文：

NCHAR(n)
NCHR(n)

nに指定したUnicodeを文字に変換する。

使用例：Unicodeを文字列に変換する。

SELECT unicode, NCHAR(unicode) FROM tbTestTable;

unicode 　　　NCHAR(unicode)
——————————————————
20154 　　　　人
29289 　　　　物
12373 　　　　さ
66 　　　　　　B

SQLコマンド辞典に戻る

OracleやSQL Serverで、文字列の右（末尾）から空白文字（スペース文字）を削除するにはRTRIM関数を使用します。


構文：

RTRIM(word)

wordに指定した文字列の右から空白文字を削除する。

使用例：文字列の右から空白を削除する。

SELECT word, RTRIM(word) FROM tbTestTable;

word 　　　　　　　　　　　　　　　RTRIM(word)
————————————————–
Oracle[]Master[]Silver[]　　　　Oracle[]Master[]Silver
[]MCTS[]70-431[]　　　　　　　　[]MCTS[]70-431

※[]は空白（スペース）を表す。


SQLコマンド辞典に戻る

OracleやSQL Serverで文字列の左（先頭）から空白（スペース）文字を削除するにはLTRIM関数を使用します。


構文：

LTRIM(word)

wordで指定した文字列の左側から空白文字を削除する。

使用例：文字列の先頭の空白（スペース）を削除する。

SELECT word, LTRIM(word) FROM tbTestTable;

word 　　　　　　　　　　　　　　　　LTRIM(word)
———————————————–
[]Oracle[]Master[]Bronze 　　　Oracle[]Master[]Bronze
MCTS[]70-431 　　　　　　　　　　MCTS[]70-431
[]MySQL[] 　　　　　　　　　　　　MySQL[]

※[]は空白（スペース）を表す。

SQLコマンド辞典に戻る

SQL ServerではRIGHT関数を使用して文字列の右部分（末尾）から指定した文字数だけ文字を抽出することができます。左側（先頭）から抽出する場合はLEFT関数を使用します。


文法：

RIGHT(word, n)

wordに指定した文字列の右からn文字分を抽出する。SQL Serverでは漢字も1文字として数える。

使用例：名前の右側から4文字分だけ抽出する。

SELECT name, RIGHT(name, 4) FROM tbEmpTable;

name 　　　　　　　　RIGHT(name, 4)
—————————————————————
木ノ下雄一郎 　　　下雄一郎
Ninomiya 　　　　　miya
ohtsuki 　　　　　　　suki

SQLコマンド辞典に戻る

OracleではDECODE関数を使って、値を変換する事ができます。使い方はCASE命令と似ています。CASE命令で書き換える事も可能です。


文法：

DECODE( x, target, new_value [ , target2, new_value2.....] [ , default] )

xで指定した値を評価し、それがtargetと等しい場合はnew_valueを返す。target2に等しい場合はnew_value2を返す。xの値がどの値とも一致しない場合はdefaultを返す。

使用例：result列が2の場合は’不合格’、1の場合は’合格’を返す。それ以外の場合は’集計中’を返す。

SELECT user, result, DECODE(result, 2, ‘不合格’, 1, ‘合格’, ‘集計中’) FROM tbExamTable;

user 　　result 　DECODE(result, 2, ‘不合格’, 1, ‘合格’, ‘集計中’)
————————————————————————————-
Tom 　　　1 　　　合格
Jenny 　　 0 　　　集計中
Chris 　　 2 　　　不合格
Andrew 　 1 　　 合格

SQLコマンド辞典に戻る

SQL Serverで、文字列の左（先頭）から指定した文字分だけ抽出するにはLEFT関数を使います。


文法：

LEFT(word, n)

wordに指定した文字列の左からn文字分を抽出する。SQL Serverでは漢字も1文字として数える。

使用例：名前の左側から4文字分だけ抽出する。

SELECT name, LEFT(name, 4) FROM tbEmpTable;

name 　　　　　LEFT(name, 4)
————————————————————
Yamashita 　　Yama
Tanaka 　　　　Tana
田中浩一郎　　田中浩一

SQLコマンド辞典に戻る

SQL Serverで数値を文字列に変換するにはSTR関数を使います。


文法：

STR(n [ , length [ , decimal] ] )

nで与えられた数値を文字列に変換する。lengthで指定した文字列の長さで変換することができる。lengthの指定がない場合、デフォルトで10桁の長さの文字列を返す。decimalの指定で、小数点の桁数を指定できる。


使用例：number列の数値を文字列に変換する。

SELECT number, STR(number) FROM tbTestTable;

number 　　　STR(number)
—————————————–
3 　　　　　　　　　　　　　3
10 　　　　　　　　　　　10
3.14 　　　　　　　　　　　3

使用例：小数点2桁まで変換する。

SELECT number, STR(number, 10, 2) FROM tbTestTable;

number 　　　STR(number, 10, 2)
——————————————–
3 　　　　　　　　　　　　3.00
10 　　　　　　　　　　　10.00
3.14 　　　　　　　　　　3.14

SQLコマンド辞典に戻る

SQL ServerではUNICODE関数を使って、文字をユニコードに変換できます。


文法：

UNICODE(word)

wordに指定した文字のユニコードを返す。wordに2文字以上の文字列を指定した場合は、1文字目のUnicodeを返します。

使用例：文字のUnicodeを求める。

SELECT word, UNICODE(word) FROM tbTestTable;

word 　　　UNICODE(word)
——————————————————–
a 　　　　　97
b 　　　　　98
3 　　　　　51
山 　　　　23665
か 　　　　12363
かわ 　　　12363

SQLコマンド辞典に戻る

OracleではTRANSLATE関数を使用して文字列の置換を行う事ができます。


文法：

TRANSLATE(word, A, B)

wordの文字列Aを文字列Bに置換する。例えば、Aに’abc’とあり、Bに’123′と指定した場合、文字列のaを1、bを2に、cを3に置換する。決して’abc’という連続した文字列を対象とするのではない。


使用例：文字列のxyzをそれぞれ123に置換する。

SELECT word, TRANSLATE(word, ‘xyz’, ’123′) FROM tbTestTable;

word 　　　　　TRANSLATE(word, ‘xyz’, ’123′)
——————————————————————-
mixi 　　　　　mi1i
zexy 　　　　　3e12
apple 　　　　apple

使用例：数字を全て$に置換する。

SELECT card, TRANSLATE(card, ’0123456789′, ‘$$$$$$$$$$’) FROM tbEmpTable;

card 　　　　　　　　　　　　　　　TRANSLATE(salary, ’0123456789′, ‘$$$$$$$$$$’)
———————————————————————————————
0023-4444-1123-123 　　　　　$$$$-$$$$-$$$$-$$$
02203-112-22-22-254 　　　　　$$$$$-$$$-$$-$$-$$$
01230-445-6664-333 　　　　　$$$$$-$$$-$$$$-$$$

SQLコマンド辞典に戻る

Oracleでは、INSTR関数を使用して文字列の位置を求める事ができます。

文法：

INSTR(word, target [, N [, M] ] )

wordで指定した文字列からtargetを検索して、位置を求めます。Nを指定している場合はN番目の文字から検索を開始します。Mを指定している場合はM番目に検索されるtargetの位置を返します。N、Mを省略すると、共に1として扱われます。
targetを最後（末尾）から検索するにはNに負の数を指定します。

使用例：メールアドレスの@の位置を求める。

SELECT email, INSTR(email, ‘@’) FROM tbEmpTable;

email　　　　　　　　　　　　　　　　 INSTR(email, ‘@’)
————————————————————
Oyamada@sql.jpn.org　　　　 　　8
tani@sql-master.net 　　　　　　　5
infomation@sql-master.net 　　　11

使用例：電話番号から?（ハイフン）が2番目に見つかった位置を求める。

SELECT tel, INSTR(tel, ‘ – ‘, 1, 2) FROM tbEmpTable;

tel 　　　　　　　　　INSTR(tel, ‘ – ‘, 1, 2)
—————————————————————–
04-111b-234# 　　　8
032-22-bb398 　　　7
003405#1222 　　　0

使用例：メールアドレスの@を最後（末尾）から検索して位置を求める。

SELECT email, INSTR(email, ‘@’, -1) FROM tbEmpTable;

email　　　　　　　　　　　　　　　　 INSTR(email, ‘@’)
————————————————————
Oyamada@sql.jpn.org　　　　 　　12
tani@sql-master.net 　　　　　　　15
infomation@sql-master.net 　　　15

SQLコマンド辞典に戻る

SQL Serverでは、REPLICATE関数を使って文字列を指定回数繰り返す事ができます。


文法：

REPLICATE(s, n)

sに指定した文字列をn回繰り返す。

使用例：社員名列をN列の回数分だけ繰り返して表示する。

SELECT name, N, REPLICATE(name, N) FROM tbTestTable;

name 　　　　N　　　　　 REPLICATE(name, N)
—————————————————————
Yamada 　　3 　　　　　YamadaYamadaYamada
Kawai 　　　　2 　　　　KawaiKawai
Urabe　　　 -2 　　　　　(NULL)
Okawa　　　 0

SQLコマンド辞典に戻る

SQL ServerではSPACE関数を使ってスペース文字を返す事ができます。


文法：

SPACE(n)

長さn個のスペース文字を作成する。


使用例：姓と名の間に3個のスペース文字を作成する

SELECT surname + givenname, surname + SPACE(3) + givenname FROM tbTestTable;

surname + givenname 　　　　surname + SPACE(3) + givenname
—————————————————————————————————-
YamadaYoichi 　　　　　　　　　Yamada　　Yoichi
TanakaHanako 　　　　　　　　　Tanaka　　Hanako
OkitaYusuke 　　　　　　　　　　Okita　　Yusuke

SQLコマンド辞典に戻る

SQL Serverで文字列の部分的な置換を行う場合はSTUFF関数を使用します。


文法：

STUFF(a, b, c, d)

aで指定した文字列のb番目からc文字分の文字をdと入れ替える。

使用例：文字列の2番目から3文字分の文字を###で置換する。

SELECT word, STUFF(word, 2, 3, ‘###’) FROM tbTestTable;

word　　　　　　　 STUFF(word, 2, 3, ‘###’)
——————————————————————-
Japanese 　　　　J###nese
Monday 　　　　　M###ay
memorial 　　　　m###rial

SQLコマンド辞典に戻る

OracleではCONCAT関数を使用して文字列を結合することができます。


文法：

CONCAT( a, b)

文字列aと文字列bを結合する

使用例：姓列と名列を結合する

SELECT surname, givenname, CONCAT(surname, givenname) FROM tbEmpTable;

surname 　　givenname 　　CONCAT(surname, givenname)
———————————————————————————–
田中　　　　　一郎　　　　　　　田中一郎
山田　　　　　太郎　　　　　　　山田太郎
川崎　　　　　洋子　　　　　　　川崎洋子

SQLコマンド辞典に戻る

文字列の長さを取得するには、SQL Serverの場合はLEN関数、Oracleの場合はLENGTH関数を使用する。


文法：

LEN(word)
LENGTH(word)

wordに指定した文字列の長さを返す。

使用例：文字列の長さを求める

SELECT word, LEN(word) FROM tbTestTable;

word 　　　　LEN(word)
—————————————————–
abcd 　　　　4
I’m Jenny 　9
東京駅 　　　3

SQLコマンド辞典に戻る

SQL Serverでデータの型の変換を行う場合は、CONVERT関数を使います。


文法：

CONVERT(type, value, [stype])

valueをtypeの型に変換する。styleで指定した変換スタイルを利用して日付型のフォーマットを指定する事も可能。


使用例：数値を文字列に変換する

SELECT CONVERT(varchar, 1234);

CONVERT(varchar, 1234)
———————————————–
1234

使用例：文字列を数値に変換する

SELECT CONVERT(int, ’1234′)

CONVERT(int, ’1234′)
———————————————
1234

使用例：文字列を日付に変換する

SELECT CONVERT(datetime, ’09/08/31′)

CONVERT(datetime, ’09/08/31′)
————————————————–
2009-08-31 00:00:00.000

SQLコマンド辞典に戻る