データを置換したり切り取ったり、型変換したりする方法を調べたらここにまとめています
#文字の切り取り
文字列の一部を切り取る方法
前方、後方、一部分など
##SUBSTRING
何文字目から何個分文字を切り取るか指定する関数
名前の2文字目1文字を抜き取るならこんな感じ。
SELECT SUBSTRING(first_name, 2, 1) FROM first_grader
「五十嵐」さんなら「十」が抽出される
##LEFT
左から何個分の文字を切りとるかを指定する関数
名前の先頭文字を切りとるならこんな感じ
SELECT LEFT(first_name, 1) FROM first_grader
「五十嵐」さんなら「五」が抽出される
##RIGHT
右から何個分の文字を切りとるかを指定する関数
名前の最後の１文字を切りとるならこんな感じ
SELECT RIGHT(first_name, 1) FROM first_grader
「五十嵐」さんなら「嵐」が抽出される
#文字の置換
部分的に書き換えたいときに使う
もし対象がなければそのまま表示される
ReplaceとTranslateがあり、RDBMSによって使える使えないがあるらしい。ややこし。
##REPLACE
こちらは「Access」「SQLServer」「Oracle」「PostgreSQL」で使える
SELECT REPLACE(first_name, '十', '百') FROM first_grader
「五十嵐」さんなら「五百嵐」さんにレベルアップ？する
##TRANSLATE
こちらは「Oracle」「PostgreSQL」「MySQL」で使える。つまりMicroSoft系では使えない。
SELECT TRANSLATE(first_name, '十', '百') FROM first_grader
書き方も結果も同じですね
#フォーマット変換
データの本質は変えない？けど、見え方を変えるやつ。
##FORMAT
数字や日時を都合のいい形に変更します。
前方ゼロ埋めや有効桁数、日時に"/"を入れたり入れなかったりしたい時に使います。
MS系のOSやDBMSで使える型はこちらのサイトの下の方に一覧で書かれています。
SELECT FORMAT(student_number, '0000')
FROM first_grader
これで数値が前方ゼロ埋めされた4桁の文字列が返されるので、"1"なら"0001"になる。
今回は省略しているけど、第三引数(culture)を指定すると、便利にできるらしい。
##CONVERT
型を変換する。日付型や数字を文字列にすることができる。
SELECT CONVERT( VARCHAR(10), 100) + student_name
FROM first_grade
数字と文字列は型が違うので文字列として結合ができないが、
CONVERTを使って数字を文字列に変換すると結合することができる。
SELECT CONVERT( VARCHAR(30), admission_day, 111)
FROM first_grade
日付型のデータを文字列に変換している。
第三引数に渡している数字で変換後の形が決まり、
上記"111"の場合は"yyyy/mm/dd"の形になる。
SELECT CONVERT( DATETIME, '2020-01-01 15:21:08')
FROM first_grade
#日付操作
日付の加算減算や、月初日の取得など
##DATEADD
DATE型のデータに対して、年月日や時間の加算、減算をおこなう。
下の例文では1ヶ月前を出力している。
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
表示方法を変換して文字の置換をすることにより、月初日や月末などを取得することができる。
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"の月初になる。
SELECT DATEADD( DAY, -1, CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, admission_day, 112), 6)+'01'))
FROM first_grade
先ほどの要領でadmission_dayの月初日を取得して、1日減算することで前月の月末日を取得することができる。
SELECT CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, DATEADD(MONTH, -1, admission_day), 112), 6)+'01')
FROM first_grade
GET_LastDayのDAYをMONTHに変えても取得できるが、CONVERTをする前にDATEADDで1月前にして、その月初日を取得することもできる。