SQL Procedure - i think - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Procedure - i think

Author

Topic

M8KWR
Starting Member

26 Posts

Posted - 2007-08-03 : 05:35:56

I am doing some coding in VS 2005, and was wondering if it would be better to create a procedure to try and do what i require.

I have this vb code.


        Dim SingleCharacter As String
        Dim TwoCharacters As String
        Dim ThreeCharacters As String
        Dim FourCharacters As String
        Dim FiveCharacters As String
        Dim CellContentsLength As Integer

        Dim Clean As String
        Dim Clean1 As String = ""

        Dim Pos, A_Char$

        Pos = 1


        For Pos = 1 To Len(Me.TextBox1.Text)
            A_Char$ = Mid(Me.TextBox1.Text, Pos, 1)
            If A_Char$ >= "0" And A_Char$ <= "9" Then
                Clean1 = Clean1 + A_Char$
            End If
        Next Pos



        SingleCharacter = Mid(Clean1$, 1)
        TwoCharacters = Mid(Clean1$, 1, 2)
        ThreeCharacters = Mid(Clean1$, 1, 3)
        FourCharacters = Mid(Clean1$, 1, 4)
        FiveCharacters = Mid(Clean1$, 1, 5)


        If TwoCharacters = "44" And Not ThreeCharacters = "440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = "0" & Mid(Clean1$, 3, CellContentsLength)


        ElseIf TwoCharacters = "44" And ThreeCharacters = "440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = Mid(Clean1$, 3, CellContentsLength)


        ElseIf ThreeCharacters = "044" And Not FourCharacters = "0440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = "0" & Mid(Clean1$, 4, CellContentsLength)


        ElseIf ThreeCharacters = "044" And FourCharacters = "0440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = Mid(Clean1$, 4, CellContentsLength)


        ElseIf FourCharacters = "0044" And Not FiveCharacters = "00440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = "0" & Mid(Clean1$, 5, CellContentsLength)


        ElseIf FourCharacters = "0044" And FiveCharacters = "00440" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = Mid(Clean1$, 5, CellContentsLength)


        ElseIf Not SingleCharacter = "0" And Not SingleCharacter = "" Then
            CellContentsLength = Len(Clean1$)
            Clean$ = "0" & Mid(Clean1$, 1, CellContentsLength)

        Else
            Clean$ = Clean1$
        End If



        Me.TextBox1.Text = Clean$.ToString

Bascially what the code does is to take a phone number, and reformat it so it can be used to cross reference another table (in that tables format)

Can something like this be done when creating a new table reformat existing phone number that i am updating that table to??

Many thanks for help or advice in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources