日時を扱ういろいろ MySQL版
※MySQLを前提として書いています
現在日時の取得
-- YYYY-MM-DD hh:mm:ss SELECT NOW()
月の最終日の取得
-- 2013-06-10 を渡すと 2013-06-30 SELECT LAST_DAY(日付)
フォーマットして取得
SELECT DATE_FORMAT(日付, フォーマット)
フォーマット部分
- %Y 西暦年(4桁)
- %y 西暦年(2桁)
- %m 月(2桁ゼロ詰め)
- %c 月(ゼロ詰めなし)
- %d 日(2桁ゼロ詰め)
- %j 日(ゼロ詰めなし)
- %W 曜日(英語)
- %w 曜日(日:0、月:1、…、土:6)
- %k 時(24時間表記)
- %i 分
- %s 秒
フォーマット例:「2013/06/09」→ %Y/%m/%d
ここまで覚えておけば組み合わせで大体のことはできそうです
まあ覚えられないので書いてるんですけどね。
以下は蛇足。
現在日時の取得
SELECT current_timestamp -- YYYY-MM-DD hh:mm:ss ,current_date -- YYYY-MM-DD ,CURDATE() -- YYYY-MM-DD ,current_time -- hh:mm:ss ,CURTIME() -- hh:mm:ss
曜日の取得
SELECT WEEKDAY(日付) -- 月:0、火:1、…、日:6 ,DAYOFWEEK(日付) -- 日:1、月:2、…、土:7
これに加え、フォーマット指定の「%w」が「日:0、月:1、…、土:6」と
すべて戻り値が異なるので、
好みの方法で統一しておく方がリスクが少ない。
尚、PHPでのフォーマットにも
- 月:1、火:2、…、日:7
- 日:0、月:1、…、土:6
と2通りあり、合わせる必要がある場合はフォーマット指定を利用するとよい。
すごくまぎらわしい。
マルチバイト文字列関数の罠
ある時、CSVのデータをインポートする処理で不具合が発生。
「2件のデータをまとめてインポートすると1件のみ失敗して、
成功したデータを除いて再度その1件のみをインポートすると成功した」
いやいやそんなアホな。
色々調べた結果、この結論に至りました。
「マルチバイト文字列関数を使用するときは文字コードを指定した方が安全かもしれない」
…うーん…。
今でも微妙に納得いかないのですが。
ちなみに、当該箇所の処理はこうです。
- CSVを取り込む
- 改行区切りで1セットのデータに分割
- カンマ区切りでデータ内の各項目に分割して文字配列にする
- ある項目を全角数字から半角数字に変換(mb_convert_kanaを使用)
- 他項目も含めいろいろ調整したデータをDBに入れたりいろいろする
…という感じなのですが、どうやら全角→半角にする過程で誤変換されたようです。
有効な半角数字にならず、データ不備としてはじかれた、と。
何故か、その2件のデータを一緒に取り込んだ時のみ、片方だけ。
なので特定の文字列が誤変換されるというわけではないですし、問題なく複数レコードが取り込めることだってありました。
(開発時にも、もちろんテストしましたし…)
ともかく、
>全角数字から半角数字に変換(mb_convert_kanaを使用)
これを
$hoge = mb_convert_kana($hoge, "a")
と書いていたのを
$hoge = mb_convert_kana($hoge, "a", "UTF-8")
こうすると改善されました。
誤変換しない場合の方が圧倒的に多いというのがネックですね…。
テストで気付きにくいですし。
他のマルチバイト系関数を使用する際も、文字コードが指定できるものはした方が安全かもしれません。
関係ありそうでしかもちょくちょく使う関数一覧
(リンクは公式マニュアル)
- mb_convert_kana
- 半角・全角間の変換を行う
- mb_strstr / mb_stristr / mb_strpos / mb_strrpos / mb_stripos / mb_strripos
- 文字列Aの中に文字列Bが最初(または最後)に現れる位置を探す
- mb_strlen
- 文字列の文字数を返す
- mb_substr
- 文字列の一部を返す
- mb_strtolower / mb_strtoupper
- 文字列をすべて小文字(または大文字)にする
文字列のトリム(左端・右端にある特定の文字列を削除 )
以下MySQLで確認していますが、Oracle・PostgreSQLでも同じ書き方である模様。
例えば都道府県欄に「○○県」と入っており「県」は除いて表示したい場合など、は以下のようにします。
SELECT TRIM(オプション 削除する文字列 FROM フィールド名) FROM テーブル名
オプションは、
両端から削除:BOTH (※省略可)
左端から削除:LEADING
右端から削除:TRAILING
となります。
-- 「○○県」から右端の「県」を取り除いて表示 SELECT TRIM(TRAILING '県' FROM `pref`) FROM `pref_master`
【ネタ】手作りソート
何やら「A1:A10に入力された数値データを昇順に並び変えてB1:B10に格納せよ」という学校の課題的なお題を頂いたので作ってみました。
もちろんソート機能は使用禁止です。
(つまり実務で使う意味は皆無のネタコードです)
Option Explicit 'ソート範囲 Const SORT_RANGE As String = "A1:A10" Const RESULT_RANGE As String = "B1:B10" 'ソート個数 Const SORT_COUNT As Long = 10 '難しいことを考えるのは面倒なので、 '一番小さい数字を見つけたら一番上に入れて、 '残りの数字からさらに一番小さい数字を見つけたら…を繰り返すソートにします。 Public Sub Main() 'ワークシートを定義 Dim wsSheet As Worksheet Set wsSheet = ThisWorkbook.Sheets("Sheet1") 'ソート範囲内の数字を配列にぶちこむ 'この方法だと1列しかなくても二次元配列になる。 '※(1,1)~(10,1) Dim lNumList As Variant lNumList = wsSheet.Range(SORT_RANGE) '一番小さい数字を見つけて並べ替えるのを繰り返す Dim i As Long Dim j As Long Dim lTmpNum As Long Dim lTmpAdd As Long For i = 1 To SORT_COUNT - 1 '一番小さい数字を探す lTmpNum = lNumList(i, 1) '仮数字に先頭の数字をセット lTmpAdd = i '仮数字の座標をセット For j = i + 1 To SORT_COUNT If lNumList(j, 1) < lTmpNum Then '仮数字よりも小さいものを見つけたらそっちを仮数字に lTmpNum = lNumList(j, 1) '元の一番小さい数字と新しい一番小さい数字を入れ替える lNumList(j, 1) = lNumList(lTmpAdd, 1) lNumList(lTmpAdd, 1) = lTmpNum End If Next 'この時点でlTmpNumに入っているものが一番小さい 'それ以外でまた並べ替える Next ' 結果をワークシートに書き込む wsSheet.Range(RESULT_RANGE) = lNumList End Sub
ワークシートの最大行数、最大列数を取得
最大行数は Rows.Count
最大列数は Columns.Count
で取得できます。
' 使用例(1):「Sheet1」シートの全セルのデータや書式設定を削除 With ThisWorkbook.Sheets("Sheet1") Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Clear End With ' 使用例(2):可変値が範囲外にならないようなエラーチェック Dim lRow As Long Dim lCol As Long lRow = ~~ lCol = ~~ If (0 < lRow And lRow <= Rows.Count) And (0 < lCol And lCol <= Columns.Count) Then ThisWorkbook.Sheets("Sheet1").Cells(lRow, lCol).Value = "hoge" Else MsgBox ("範囲外やで") End If
ボタンひとつで簡易grep
何度もgrepをかけるとき、いちいちウインドウ出てくることや
検索フォルダが「今開いているファイルの位置」になるのが非常に鬱陶しいので、
「あらかじめ指定されたフォルダ以下にて今選択している文字列をgrep」するマクロを作りました。
ショートカットキー割り当てておくと非常に楽です。
私は折り返し位置を81文字に設定しているので、
grep後に調整しています(コメントアウトしている部分)。
//grepフォルダ $curdir = "C:\\hoge\\hage\\"; //grep対象ファイル(拡張子とか) $filter = "*.c;*.cpp;*.h"; //選択範囲の文字列を取得 $word = gettext(seltopx,seltopy,selendx,selendy); //grep grep $word,$filter,$curdir,icon,subdir; ////折り返し位置変更 //config "w2000";
…という記事を旧ブログから移行してきたのですが、今読むと「ねぇよそんな作業」って感じです…。
かつては何故かすんげえ頻度ですんげえ大量にgrepする現場にいたので…。
VBAでセルの結合
セルを結合するには、MeageCellsプロパティを用います。
' A1~C3のセルを結合 Range("A1:C3").MergeCells = True '現在選択している範囲を結合 Selection.MergeCells = True
ここで、結合したいセルのうち2つ以上にデータが入っていると、警告が表示されます。
これを無視して(表示させずに)結合したい場合は、
あらかじめ「Application.DisplayAlerts」をFalseに設定しておけばOKです。
Application.DisplayAlerts = False Selection.MergeCells = True Application.DisplayAlerts = True
また、逆にセルの結合を解除したい場合は、もちろんMergeCellsプロパティをFalseに設定すればOKです。
頭文字検索
※MySQLを前提として書いています
用語集みたいなやつを作っていて、「あ行」「か行」…「英数字」という頭文字検索があったので、
調べてみたところLIKEかREGEXPが使えそうかなと思ったのですが…
LIKE
→複雑な正規表現は使用不可
→日本語に対応
でした。
日本語対応版REGEXPであるMREGEXPというものもあるのですが、
会社の環境なのでインストールはちょっと…。
参考
mregexp - MySQLで日本語の正規表現を扱う
LIKEを使うとしたら
-- あ行 SELECT * FROM `table` WHERE `yomi` LIKE 'あ%' OR `yomi` LIKE 'い%' OR `yomi` LIKE 'う%' OR `yomi` LIKE 'え%' OR `yomi` LIKE 'お%' -- 英数字 SELECT * FROM `table` WHERE `yomi` LIKE '0%' OR `yomi` LIKE '1%' -- (中略) OR `yomi` LIKE 'a%' OR `yomi` LIKE 'b%' -- (中略) OR `yomi` LIKE 'z%'
…これはひどい。
REGEXPを使うなら
-- あ行 SELECT * FROM `table` WHERE `hoge` REGEXP '^(あ|い|う|え|お)' -- 英数字 SELECT * FROM `table` WHERE `hoge` REGEXP '^([0-9]|[a-z])'
という形になるはず。
ダメもとで試してみたところ、
例えば「あ行」の検索だと、確かに「あ~おのいずれかで始まる単語」はすべてヒットするのですが、
他の一部の単語もヒットしてしまいました。ほんとにダメかー。
ということで、日本語頭文字はLIKE検索・英数字はREGEXP検索、とすることにしました。
MREGEXPが使用可能ならこちらを使ってもよいと思いますが、LIKEの方が速度は速いようです。
データ量や正規表現の有無を考えて、MREGEXPにしろREGEXPにしろ、ご利用は計画的に。
あとは、根本的な対策として、インデックス用フィールドみたいなのを作っても良いかもしれませんね。
「あ行」「か行」みたいなのを読みから判断するのではなく、それ自体を持たせる形で。
フィールドの順序を変更する
※MySQLを前提として書いています
あるテーブルで、フィールドが「field_a」「field_b」がこの順に存在し、これを入れ替えたいときは以下のようにします。
ALTER TABLE テーブル名 MODIFY COLUMN `field_a` 型名 AFTER `field_b`;