Finding the Last Occurrence of a Pattern in a String

When dealing with string data, one of the most useful Transact-SQL functions you can have in your toolbox is PATINDEX. Aside from it’s many creative uses (and there are quite a few), it allows you to search for an expression (including wildcard operators) within any valid SQL Server character type. It returns the position of the first occurrence of the pattern within the string, and returns zero (0) if the pattern is not found. As always, an example is worth 1000 bon mots:

SELECT PATINDEX('%c%', 'abcdefgabcdefga')

The above snippet returns 3, because the first instance of “c” within “abcdefgabcdefga” is at the third character.

On occasion, you may be required to find the last occurrence of the pattern rather than the first. This usually happens when you’re dealing with data from external systems, or when you’ve inherited custody of an inappropriately denormalized system (one where more than one “logical” data item has been stored in a single, concatenated field). I have the same requirement for my “mad science” series, so I developed a function to take care of it. Here it is:

CREATE FUNCTION dbo.REVERSEPATINDEX(@StringToFind    nvarchar(max),

                                  @StringToSearch nvarchar(max))

RETURNS bigint

AS

BEGIN

  --LastIndex will store the "previous" index of the pattern

  --while we search for the "next" index inside our string.

  DECLARE @LastIndex bigint, @NextIndex bigint


  --We will chop off the "searched" portion of our string

  --After each search iteration

  DECLARE @CurrentStringToSearch nvarchar(max)


  --Assign the value of the input parameter to @CurrentStringToSearch

  SET @CurrentStringToSearch = @StringToSearch


  --Grab the first occurrence, if it exists, using PATINDEX

  SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)

    
  --If we've found the pattern within the string, continue and find

  --subsequent occurrences until there are no more to find.

  WHILE @NextIndex > 0

  BEGIN

      --Add @NextIndex to @LastIndex, effectively storing within

      --@LastIndex the index of the occurence that we have just found,

      --based upon the original (non-truncated) string.

      SET @LastIndex = COALESCE(@LastIndex,0) + @NextIndex

    
      --Truncate @CurrentStringToSearch, removing the portion of the

      --string that we have already searched.

      SET @CurrentStringToSearch
          = RIGHT(@CurrentStringToSearch, LEN(@CurrentStringToSearch) - @NextIndex)

    
      --Find the NEXT occurence of the pattern in the remaining portion of

      --the string to search.

      SET @NextIndex = PATINDEX(@StringToFind, @CurrentStringToSearch)

  END


  --We will COALESCE @LastIndex and @NextIndex; @LastIndex will be NULL

  --if the pattern does not exist in the string (as the loop above will

  --never have been executed).

  RETURN  COALESCE(@LastIndex, @NextIndex)

END

GO

And to test:

SELECT dbo.REVERSEPATINDEX('%c%', 'abcdefgabcdefga')

Returns 10 – the correct answer, given that the last occurrence of “c” in “abcdefgabcdefga” is at the tenth character.

String manipulations

        Dim strSample As String = "Left Center Right"

        'left

        strSample = strSample.Substring(0, 4)

        'center

        strSample = strSample.Substring(5, 6)

        'right

        strSample = strSample.Substring(12)

        'split

        Dim SplitString As String = "Cat, Dog, Hen, Zebra"

        Dim SplitArr() As String

        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1

            console.writeline(SplitArr(i))

        Next

        'Join

        Dim JoinArr(2) As String

        Dim JoinString As String

        JoinArr(0) = "Give"

        JoinArr(1) = "Take"

        JoinArr(2) = "Apply"

        JoinString = String.Join(",", JoinArr)

        Console.WriteLine(JoinString)

        'insert

        Dim strTemp As String = "This is My Stng"

        strSample = strTemp.Insert(13, "ri")

        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove

        Dim RemoveString As String = "This is my Sam@o@o@ple"

        'strSample = "This is my Sample"

        strSample = RemoveString.Remove(14, 5)

        Console.WriteLine(strSample)

        'replace

        Dim ReplaceString = "This is My String"

        ' strSample = "This is Another String"

        strSample = ReplaceString.Replace("My", "Another")

        Console.WriteLine(strSample)

        'Trim Spaces

        Dim SpaceString As String = "        This string will have the spaces removed        "

        Console.WriteLine(SpaceString)

        Dim TrimmedString As String

        TrimmedString = Trim(SpaceString)

        Console.WriteLine(TrimmedString)

        'Trim Other

        Dim HashString As String = "#####Testing!######"

        TrimmedString = HashString.Trim("#")

        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)

        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString1.TrimStart(" ")

        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)

        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString.TrimEnd(" ")

        Console.WriteLine(TrimmedString)

        'Pad Left

        Dim MainString As String = "This will be padded on the left"

        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'Pad Right

        Dim MainString1 As String = "This will be padded on the right"

        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'UPPER CASE

        Dim MixedString As String = "UpPeR oR LoWeR cAsE"

        Dim UPPERString As String

        ' UPPERString = "UPPER OR LOWER CASE"

        UPPERString = MixedString.ToUpper

        Console.WriteLine(UPPERString)

        'lower case

        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"

        Dim lowerString As String

        'lowerString = "upper or lower case"

        lowerString = MixedString1.ToLower

        Console.WriteLine(lowerString)

        'TitleCase

        Dim TitleString As String = "UPPER lower Title"

        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title

        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)

        Console.WriteLine(TitleString)

        'Format

        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#

        Dim strFormattedString As String

        ' Returns current system time in the system-defined long time format.

        strFormattedString = Format(Now(), "Long Time")

        ' Returns current system date in the system-defined long date format.

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(Now(), "Long Date")

        Console.WriteLine(strFormattedString)

        ' Also returns current system date in the system-defined long date 

        ' format, using the single letter code for the format.

        strFormattedString = Format(Now(), "D")

        Console.WriteLine(strFormattedString)

        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.

        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,

        ' Jan 27 2001".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(23)   ' Returns "23".

        Console.WriteLine(strFormattedString)

        ' User-defined numeric formats.

        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".

        Console.WriteLine(strFormattedString)

        'Concat

        Dim ConcatString As String

        Dim aString As String = "A"

        Dim bString As String = "B"

        Dim cString As String = "C"

        Dim dString As String = "D"

        ' ConcatString = "ABCD"

        ConcatString = String.Concat(aString, bString, cString, dString)

        Console.WriteLine(ConcatString)

        'IndexOf

        Dim MainString2 As String = "ABCDE"

        Dim Result As Integer

        Result = MainString2.IndexOf("D")  ' result = 3

        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars

        Dim MainString3 As String = "ABCDE"

        Dim ResultChar As Char

        ResultChar = MainString3.Chars(3) ' resultChar = "D"

        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf

        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"

        Result = MainString.LastIndexOf("\")

        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare

        Dim FirstString As String = "Alphabetical"

        Dim SecondString As String = "Order"

        Dim ThirdString As String = "Order"

        Dim FourthString As String = "Alphabetical"

        Dim Result1 As Integer

        Dim Result2 As Integer

        Result1 = String.Compare(FirstString, SecondString)

        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")

        Result2 = String.Compare(ThirdString, FourthString)

        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length

        Dim MainString5 As String = "String To Test The Length"

        Dim Result3 As Integer = MainString.Length

        Console.WriteLine(Result.ToString & " Characters")

        'Copy

        Dim EmptyString As String

        Dim CopyString As String = "This Will Be Copied Into An Empty String"

        EmptyString = String.Copy(CopyString)

        Console.WriteLine(EmptyString)

String manipulations

        Dim strSample As String = "Left Center Right"

        'left

        strSample = strSample.Substring(0, 4)

        'center

        strSample = strSample.Substring(5, 6)

        'right

        strSample = strSample.Substring(12)

        'split

        Dim SplitString As String = "Cat, Dog, Hen, Zebra"

        Dim SplitArr() As String

        Dim i As Integer

        SplitArr = SplitString.Split(","c)

        For i = 0 To SplitArr.Length - 1

            console.writeline(SplitArr(i))

        Next

        'Join

        Dim JoinArr(2) As String

        Dim JoinString As String

        JoinArr(0) = "Give"

        JoinArr(1) = "Take"

        JoinArr(2) = "Apply"

        JoinString = String.Join(",", JoinArr)

        Console.WriteLine(JoinString)

        'insert

        Dim strTemp As String = "This is My Stng"

        strSample = strTemp.Insert(13, "ri")

        Console.WriteLine(strSample)    'OUTPUT: "This is My String".

        'Remove

        Dim RemoveString As String = "This is my Sam@o@o@ple"

        'strSample = "This is my Sample"

        strSample = RemoveString.Remove(14, 5)

        Console.WriteLine(strSample)

        'replace

        Dim ReplaceString = "This is My String"

        ' strSample = "This is Another String"

        strSample = ReplaceString.Replace("My", "Another")

        Console.WriteLine(strSample)

        'Trim Spaces

        Dim SpaceString As String = "        This string will have the spaces removed        "

        Console.WriteLine(SpaceString)

        Dim TrimmedString As String

        TrimmedString = Trim(SpaceString)

        Console.WriteLine(TrimmedString)

        'Trim Other

        Dim HashString As String = "#####Testing!######"

        TrimmedString = HashString.Trim("#")

        Console.WriteLine(TrimmedString)

        'Left Trim ( TrimStart)

        Dim SpaceString1 As String = "        This string will have the leading spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString1.TrimStart(" ")

        Console.WriteLine(TrimmedString)

        'Right Trim ( TrimEnd)

        Dim SpaceString2 As String = "        This string will have the trailing spaces removed        "

        Console.WriteLine(SpaceString)

        TrimmedString = SpaceString.TrimEnd(" ")

        Console.WriteLine(TrimmedString)

        'Pad Left

        Dim MainString As String = "This will be padded on the left"

        Dim PaddedString As String

        PaddedString = MainString.PadLeft(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'Pad Right

        Dim MainString1 As String = "This will be padded on the right"

        PaddedString = MainString1.PadRight(36, "@") '36 = new length of string with 5 new chars

        Console.WriteLine(PaddedString)

        'UPPER CASE

        Dim MixedString As String = "UpPeR oR LoWeR cAsE"

        Dim UPPERString As String

        ' UPPERString = "UPPER OR LOWER CASE"

        UPPERString = MixedString.ToUpper

        Console.WriteLine(UPPERString)

        'lower case

        Dim MixedString1 As String = "UpPeR oR LoWeR cAsE"

        Dim lowerString As String

        'lowerString = "upper or lower case"

        lowerString = MixedString1.ToLower

        Console.WriteLine(lowerString)

        'TitleCase

        Dim TitleString As String = "UPPER lower Title"

        TitleString = TitleString.ToLower()  ' mainly because of UPPER not changing to title

        TitleString = System.Globalization.CultureInfo.InstalledUICulture.TextInfo.ToTitleCase(TitleString)

        Console.WriteLine(TitleString)

        'Format

        Dim UnFormattedDateTime As Date = #1/27/2001 5:04:23 PM#

        Dim strFormattedString As String

        ' Returns current system time in the system-defined long time format.

        strFormattedString = Format(Now(), "Long Time")

        ' Returns current system date in the system-defined long date format.

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(Now(), "Long Date")

        Console.WriteLine(strFormattedString)

        ' Also returns current system date in the system-defined long date 

        ' format, using the single letter code for the format.

        strFormattedString = Format(Now(), "D")

        Console.WriteLine(strFormattedString)

        ' Returns the value of UnFormattedDateTime in user-defined date/time formats.

        strFormattedString = Format(UnFormattedDateTime, "h:m:s")   ' Returns "5:4:23".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "hh:mm:ss tt")   ' Returns "05:04:23 PM".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "dddd, MMM d yyyy")   ' Returns "Saturday,

        ' Jan 27 2001".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(UnFormattedDateTime, "HH:mm:ss")   ' Returns "17:04:23"

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(23)   ' Returns "23".

        Console.WriteLine(strFormattedString)

        ' User-defined numeric formats.

        strFormattedString = Format(5459.4, "##,##0.00")   ' Returns "5,459.40".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(334.9, "###0.00")   ' Returns "334.90".

        Console.WriteLine(strFormattedString)

        strFormattedString = Format(5, "0.00%")   ' Returns "500.00%".

        Console.WriteLine(strFormattedString)

        'Concat

        Dim ConcatString As String

        Dim aString As String = "A"

        Dim bString As String = "B"

        Dim cString As String = "C"

        Dim dString As String = "D"

        ' ConcatString = "ABCD"

        ConcatString = String.Concat(aString, bString, cString, dString)

        Console.WriteLine(ConcatString)

        'IndexOf

        Dim MainString2 As String = "ABCDE"

        Dim Result As Integer

        Result = MainString2.IndexOf("D")  ' result = 3

        Console.WriteLine("D is Character number : " & Result.ToString)

        'Chars

        Dim MainString3 As String = "ABCDE"

        Dim ResultChar As Char

        ResultChar = MainString3.Chars(3) ' resultChar = "D"

        Console.WriteLine("Character 3 is : " & ResultChar.ToString)

        'LastIndexOf

        Dim MainString4 = "A\B\C\D\E\F\G\H\I\J"

        Result = MainString.LastIndexOf("\")

        Console.WriteLine("Last occurence of ' \ ' is at Position : " & Result.ToString())

        'Compare

        Dim FirstString As String = "Alphabetical"

        Dim SecondString As String = "Order"

        Dim ThirdString As String = "Order"

        Dim FourthString As String = "Alphabetical"

        Dim Result1 As Integer

        Dim Result2 As Integer

        Result1 = String.Compare(FirstString, SecondString)

        Console.WriteLine("Result = " & Result1.ToString & Environment.NewLine & "First String Is Smaller Than Second")

        Result2 = String.Compare(ThirdString, FourthString)

        Console.WriteLine("Result = " & Result2.ToString & Environment.NewLine & "Third String Is Greater Than Fourth")

        'Length

        Dim MainString5 As String = "String To Test The Length"

        Dim Result3 As Integer = MainString.Length

        Console.WriteLine(Result.ToString & " Characters")

        'Copy

        Dim EmptyString As String

        Dim CopyString As String = "This Will Be Copied Into An Empty String"

        EmptyString = String.Copy(CopyString)

        Console.WriteLine(EmptyString)

Handling Keystrokes in DataGridView

Private Sub dgv_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgv.EditingControlShowing
 
If Me.dgv.CurrentCell.ColumnIndex = 0 And Not e.Control Is Nothing Then
    Dim tb As TextBox = CType(e.Control, TextBox)
    AddHandler tb.KeyDown, AddressOf TextBox_KeyDown
    AddHandler tb.KeyPress, AddressOf TextBox_KeyPress
End If
 
End Sub
 
Private Sub TextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
 If  e.KeyCode = Keys.Space Then
     flag = True
 End If
End Sub
 
Private Sub TextBox_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
 e.Handled = flag
 flag = False
End Sub

Export SQL table to excel

Dim excel As New Excel.Application
        Dim wBook As Excel.Workbook
        Dim wSheet As Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dt As DataGridView = TblJobMethodsDataGridView.DataSource.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "D:\ss.xls"
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True