データを置換したり切り取ったり、型変換したりする方法を調べたらここにまとめています

#文字の切り取り
文字列の一部を切り取る方法
前方、後方、一部分など

##SUBSTRING
何文字目から何個分文字を切り取るか指定する関数
名前の2文字目1文字を抜き取るならこんな感じ。

SUBSTRING

SELECT SUBSTRING(first_name, 2, 1) FROM first_grader

「五十嵐」さんなら「十」が抽出される

##LEFT
左から何個分の文字を切りとるかを指定する関数
名前の先頭文字を切りとるならこんな感じ

LEFT

SELECT LEFT(first_name, 1) FROM first_grader

「五十嵐」さんなら「五」が抽出される

##RIGHT
右から何個分の文字を切りとるかを指定する関数
名前の最後の１文字を切りとるならこんな感じ

RIGHT

SELECT RIGHT(first_name, 1) FROM first_grader

「五十嵐」さんなら「嵐」が抽出される

#文字の置換
部分的に書き換えたいときに使う
もし対象がなければそのまま表示される
ReplaceとTranslateがあり、RDBMSによって使える使えないがあるらしい。ややこし。

##REPLACE
こちらは「Access」「SQLServer」「Oracle」「PostgreSQL」で使える

REPLACE

SELECT REPLACE(first_name, '十', '百') FROM first_grader

「五十嵐」さんなら「五百嵐」さんにレベルアップ？する

##TRANSLATE
こちらは「Oracle」「PostgreSQL」「MySQL」で使える。つまりMicroSoft系では使えない。

TRANSLATE

SELECT TRANSLATE(first_name, '十', '百') FROM first_grader

書き方も結果も同じですね

#フォーマット変換
データの本質は変えない？けど、見え方を変えるやつ。

##FORMAT
数字や日時を都合のいい形に変更します。
前方ゼロ埋めや有効桁数、日時に"/"を入れたり入れなかったりしたい時に使います。
MS系のOSやDBMSで使える型はこちらのサイトの下の方に一覧で書かれています。

Format

SELECT FORMAT(student_number, '0000')
   FROM first_grader

これで数値が前方ゼロ埋めされた4桁の文字列が返されるので、"1"なら"0001"になる。
今回は省略しているけど、第三引数(culture)を指定すると、便利にできるらしい。

##CONVERT
型を変換する。日付型や数字を文字列にすることができる。

Convert

SELECT CONVERT( VARCHAR(10), 100) + student_name
   FROM first_grade

数字と文字列は型が違うので文字列として結合ができないが、
CONVERTを使って数字を文字列に変換すると結合することができる。

Convert_2

SELECT CONVERT( VARCHAR(30), admission_day, 111)
   FROM first_grade

日付型のデータを文字列に変換している。
第三引数に渡している数字で変換後の形が決まり、
上記"111"の場合は"yyyy/mm/dd"の形になる。

Convert_3

SELECT CONVERT( DATETIME, '2020-01-01 15:21:08')
   FROM first_grade

#日付操作
日付の加算減算や、月初日の取得など
##DATEADD
DATE型のデータに対して、年月日や時間の加算、減算をおこなう。
下の例文では1ヶ月前を出力している。

DATEADD

SELECT DATEADD(MONTH, 1, admission_day)
   FROM first_grade

MONTH以外にも多くの時間表現を操作することができる。

記述シンボル	省略形
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

##CONVERT & DATEADD & LEFT
表示方法を変換して文字の置換をすることにより、月初日や月末などを取得することができる。

GET_FirstDay

SELECT CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, admission_day, 112), 6)+'01')
   FROM first_grade

admission_dayが"2020/04/06"とすると、
CONVERT(VARCHAR, admission_day, 112)は"20200406"となり、
LEFT(CONVERT(VARCHAR, admission_day, 112), 6)は左から6桁を取得するので"202004"となる。
これの右端に"01"をつけてDATETIME型に再度CONVERTすると、"2020/04/01"の月初になる。

GET_LastDay

SELECT DATEADD( DAY, -1, CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, admission_day, 112), 6)+'01'))
   FROM first_grade

先ほどの要領でadmission_dayの月初日を取得して、1日減算することで前月の月末日を取得することができる。

GET_FirstDayOfLastMonth

SELECT CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -1, admission_day), 112), 6)+'01')
   FROM first_grade

GET_LastDayのDAYをMONTHに変えても取得できるが、CONVERTをする前にDATEADDで1月前にして、その月初日を取得することもできる。