指定した文字列の出現位置を調べる

文法
INSTR ( string , searchstring [, position [, occurence]] )

文字列 string 中の 検索文字列 searchstring を検索し、その検索文字列が現れた 文字の位置を示す数を戻す

INSTRB ( string , searchstring [, position [, occurence]] )

文字列 string 中の 検索文字列 searchstring を検索し、その検索文字列が現れた バイト位置を示す数を戻す

引数
string 文字列
searchstring 検索する文字列
position 検索対象文字列の検索開始位置(1~) デフォルトは1
occurence

取り出すまでの検知回数

使用例

select
instr ('開発部','部'),
instrb('開発部','部'),
instr('あいあいあい','あい',1,1),
instr('あいあいあい','あい',1,2),
instr('あいあいあい','あい',1,3)
from dual

結果
INSTR 使用例

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

修正履歴

Loading