文字列の一部を取り出す
SUBSTR ( string , position [, length] ) | 文字列stringの指定位置positionからの文字列を文字単位で取り出す。 |
---|---|
SUBSTRB ( string , position [, length] ) | 文字列stringの指定位置positionからの文字列をバイト単位で取り出す。 |
string | 文字列 |
---|---|
position | 取り出したい文字列の開始位置(1~) 負数にした場合は文字列の最後から逆に数えた位置となる。 |
length | 取り出したい文字長、バイト数 |
使用例
select DNAME, substr(DNAME,2), substr(DNAME,2,2), substrb(DNAME,2,3), substrb(DNAME,-2) from DEPT
結果(わかりやすいように半角スペースは * に変換して表示しています)
Imports Oracle.DataAccess.Client Public Class frmSQL_Trim Private dt As DataTable = Nothing Private Sub InitializeGrid() Grid1.Splits(0).ColumnCaptionHeight = Grid1.Splits(0).ColumnCaptionHeight * 2 End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try If TextSQL.Text.Trim.Length > 0 Then Using da As New OracleDataAdapter(TextSQL.Text.Trim, clsGlobal.gConnetionString) dt = New DataTable da.Fill(dt) If CheckBox1.Checked Then cnvSpace(dt) End If 'Gridにバインド Grid1.DataSource = dt End Using End If Catch ex As OracleException MessageBox.Show(ex.Message) End Try End Sub Private Sub cnvSpace(ByVal SourceDT As DataTable) Dim i As Integer Dim j As Integer Dim wkString As String = "" Dim wkSB As New System.Text.StringBuilder Try Dim ColCount As Integer = SourceDT.Columns.Count Dim drow As DataRow For Each drow In dt.Rows For i = 0 To ColCount - 1 If Not IsDBNull(drow.Item(i)) Then wkSB.Remove(0, wkSB.Length) wkString = drow.Item(i).ToString For j = 0 To wkString.Length - 1 If wkString.Substring(j, 1) = Space(1) Then wkSB.Append("*") Else wkSB.Append(wkString.Substring(j, 1)) End If Next drow.Item(i) = wkSB.ToString End If Next Next Catch ex As Exception Throw ex End Try End Sub Private Sub frmSQL_Trim_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load InitializeGrid() End Sub End Class