SQLServer

整数値を3桁カンマ区切りに整形するユーザ定義関数

なんか昔エントリ書いた気がするんだけど、軽く検索したら見つからなかったので備忘録に残しておきます。 元ネタは こちらです↓ 解説もこちらです↓ CONVERTを使いこなそう!! ~数値編~|ソフトウエア開発部(システム開発・システム設計 株式会社アイロベ…

任意の基数の文字列を整数値に変換するユーザー定義関数

恥ずかしながら、再帰CTEを知らずに、 Doukaku.org - 知った元ネタ:16 進数から 10 進数への変換 - ぐるぐる~ をセンス無いと酷評して、渦巻きな人とちょっと喧嘩気味になっちゃいましたが、再利用性を考慮して個人的にはユーザ定義関数で実装しておきます…

日本郵便謹製のKEN_ALL.CSVをBULK INSERT一発で取り込む

はじめに地雷を撤去しておきましょう 結果論から行くと use ZIPDB select code, count(code) as count from dbo.zipcode_master group by code having count(code) > 1 order by count(code) desc みたいなクエリを実行してみたら、重複レコードがある郵便番…

プレフィックスやサフィックスを取得する

目的とする文字列が「社長」だったとして、「副社長」の「副」*1を取得したり「社長代理」の「代理」*2を取得することを考えます。取得元の文字列には目的とする文字列が必ず含まれるとして、プレフィックスとサフィックスの両方は含まれないとします。下記…

全角スペースを含む文字列でTrimしたい

照合順序(Collation)とは SQLServerには高機能な照合順序(Collation)の設定機能があります。 日本語関連だとこちら、World Wideにはこちら*1にまとめてあります。REPLACE関数だとこんな感じ↓ -- 普通は COLLATE Japanese_CI_ASになってるでしょう SELECT REP…

HDDのフラグメントで接続がタイムアウトする

極端な例だとは思うけど、DBエンジンがローカルの場合は接続できるけど、リモートからだと接続できないという現象が出た。リモートでもODBC経由だと接続できたという不可解な現象も出た。問題のマシンは、CPU使用率は特に異常ないしメモリ不足でもない。ただ…

データベース間でテーブルの作成状況を比較

こことかこことかここによれば、データベースSOMEDBv1内のテーブル名一覧は下記のクエリで取得可能。 USE SOMEDBv1 SELECT name FROM sysobjects WHERE xtype = 'U' これの応用で、例えばアプリケーションのバージョンが上がったりしたときとかにバージョン1…

JOINするときにINNER or LEFT or RIGHT or FULLを省略するとどうなるだっけ?

他人が書いた変更対象のソースコードに SELECT a,b FROM AAA JOIN BBB ON b = a みたいなのが出てきて思考停止。普段は明示的にINNER or LEFT or RIGHT or FULLを書いているのでどの扱いになるのかまるで判らない。調べようとしてもうまくぐぐれない。orzな…

個人情報の重複登録を避けるための一意インデックス

餅は餅屋に頼みましょう。クレジットカード会社勤務歴25年の方のエントリ同姓同名 - クレジットカードの裏情報と雑学が参考になります。こんなエントリを探してもらえるのもGoogle先生様様ですね。エントリによれば、おおむね氏名・生年月日・住所で一意イ…

SQLServerで和暦を扱う

MSDNフォーラムのこのスレッドが有用です。↓http://forums.microsoft.com/MSDN-JA/ShowPost.aspx?PostID=710925&SiteID=7その中で引用されてる PASSJ 理事の河端さんのコーディング例が感動ものです。↓http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_id=1&…

TSVファイルをBULK INSERTで取り込む

CSVファイルがカンマ区切りであったのに対し、TSVファイルとはタブ区切りです(念のため)。Excelでセルの書式を壊さずにペーストできたり、プログラム上の扱いがCSVよりも簡単なのでTSVの方が好きです。CSVファイルはこっちでやりましたが、今回はTSVファイル…

CSVファイルをBULK INSERTで取り込む

必要になるたびにぐぐってSQL-Server2000 BULK INSERTでCSVファイルをインポート: 使えないプログラマーを見てるので備忘録的メモ。φ(_ _)m USE MyDB BULK INSERT dbo.○○○マスタ FROM '\\SOMESERVER\SomeShare\Some\Directory\○○○マスタ.txt' WITH ( DATAFIL…

SQL CLR関数のお勉強の出発点

SQL CLR: 気長に日記が良さそうです。3種類のSQL CLR関数についてのMSDNへのリンクが網羅されているので、出発点として良いでしょう。ただし、自分でMSDNを読まない人には価値のない情報になってしまいます。Express EditionでSQL CLR関数を作るのは確かに面…

2000以前ではSELECT IDENTITY AS hoge INTO T1 FROM T2 ORDER BY fugaしちゃいけない!

ってすごい罠だと思う。orzSELECT INTO でソートした値にIDENTITYをふるには − Database Expert − @ITのかめたろさんの発言のKnowledge Baseに書いてあった。CREATE TABLEを書くのが面倒だから、SELECT INTO FROMしてるのにーーー!http://support.microsof…

Access95の*.mdbファイルをSQLServerのリンクサーバで使おうとすると

SQL Server から Access ファイル(MDB)のデータを取得・更新する方法を参考にやってみたら、こんなふうに怒られました。 リンク サーバー "MY_LINK_SERVER" の OLE DB プロバイダ "SQLNCLI" から、メッセージ "権限の指定が正しくありません。" が返されま…

Visual Studio 2005でT-SQLユーザ定義関数&ストアドプロシージャのステップ実行

SQL Server 2000のクエリアナライザでステップ実行する例はぐぐると割と見つかるのですが、私の環境ではうまく動かないので別の方法を探したら、SQLServer2000でのSQLのステップ実行|ITプロフェッショナル部(システム開発のアイロベックス|東京都新宿区の…

bcpコマンドでテーブルをタブ区切りテキストファイル経由でコピー

元ネタはbcp.exe を使用した CSV 入出力CSVファイルへの書き出し・取込の例は多いですが、私はフィールド値にカンマが入っててハマったのでタブ区切りにしました。上がエクスポート、下がインポートです。直接SQL Server Management StudioやEnterprise Mana…

SQLServerのDateTime型の下限値

Date Time in Sql Serverによると1753/01/01だそうだ。あと、http://www.microsoft.com.nsatc.net/japan/technet/prodtechnol/windows2000serv/technologies/iis/deploy/depovg/mysql.mspxも。1900/01/01、1800/01/01、1700/01/01と入力したら、1700年でいき…

SQLServerで”YYYY/MM/DD”形式で日付を得る

すごいバカちょんですが、こんな感じでできました。 SELECT LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', '/'), 10) 可読性が最悪なので、関数にしておきませう。 CREATE FUNCTION [dbo].[GetFullDateWithSlash](@date DATETIME) RETURNS VARCH…

OPENROWSETを使う前に必要な設定

SQL ServerでOPENROWSETを使う前に明示的に許可してあげないとダメらしい。 セキュリティ的に問題があるとのこと。 SQL Injectionでローカルファイルが破壊されたり、 ローカルファイルが読み出されたりするということか? EXEC sp_configure 'show advanced…

SQLServerでの文字列の半角文字から全角文字への変換

データの全角半角変換、、 - QA@ITでじゃんぬねっとさんが言及している関数を実装してみました。#この掲示板の議論で、じゃんぬねっとさんはtypoしてますねw #やはり、人間はtypoから逃れられないのかw※超重要:私もtypoしてました。コメントでご指摘い…

*.mdfの移動にご用心

SQL Server 2005 Express Editionでテスト環境を作ってました。最近、動作があまりにも遅いのでPerfectDisk 7.0でデフラグしましたが、*.mdfの断片化が400 overと顕著です。SQLServerのサービスの停止後に*.mdfをNASへ退避し最適化を行い、その後戻すことに…

SQLServerでのMidB関数

レガシーマイグレーションで帳票の印字内容で全角・半角文字を意識した処理があり、バイト数指定の文字列関数が必要になりました。 http://blogs.sqlpassj.org/yoshihirokawabata/archive/2006/08/03/17581.aspxにLeftB, RightBはあったので、MidB関数の第3…

VB6.0アプリケーションがRDO経由でSQL Server 2005に接続できない

VB6.0アプリケーションがRDO経由でSQL Server 2005に「429 ActiveXコンポーネントはオブジェクトを作成できません」で接続できなかったんですが、 Download Visual Basic 6.0 Service Pack 6:ランタイム再頒布可能パッケージ (vbrun60sp6.exe) from Official…

リンクサーバーの罠 on SQLServer

SQLServer Management StudioのGUIから or システムプロシージャsp_addlinkedserverを実行してリンクサーバーのインスタンスを作るとSELECT文を発行するとふつーに結果が返ってくるので、安心してました。今作ってるシステムのリンクサーバーを前提に設計中…