ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    SQL Question - Removing Part Of A Field

    IT Discussion
    lightswitch sql server sql t-sql
    5
    18
    3.9k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • JaredBuschJ
      JaredBusch
      last edited by

      @garak0410 said:

      Select Left(PHONE1, 10)

      This is the correct SQL to do what you want using a normal SQL database call. If that does not work I would skip trying to do it with SQL.

      SELECT
      Phone
      ,LEFT(Phone,10)
      ,RIGHT(Phone,4)
      FROM Customer

      JaredBuschJ 1 Reply Last reply Reply Quote 0
      • JaredBuschJ
        JaredBusch @JaredBusch
        last edited by

        @garak0410 said:

        Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
            ' results.AddPropertyError("<Error-Message>")
            Select Left(PHONE1, 10)
            End Select
        End Sub
        

        Wait wait wait.. you are using a case statement? where is the rest of this statement...

        P 1 Reply Last reply Reply Quote 0
        • JaredBuschJ
          JaredBusch
          last edited by

          This post is deleted!
          1 Reply Last reply Reply Quote 0
          • JaredBuschJ
            JaredBusch
            last edited by JaredBusch

            @JaredBusch said:

            Wait wait wait.. you are using a case statement? where is the rest of this statement...

            This is what a select case statement is for in VB

            Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                ' results.AddPropertyError("<Error-Message>")
                Dim PHONE1 As String = "12345678901234"
                Select Case Mid(PHONE1, 0, 10)
                    Case "1234567890"
                        'do something here.
                    Case Else
                        'do something else.
                End Select
            End Sub
            
            garak0410G 1 Reply Last reply Reply Quote 0
            • JaredBuschJ
              JaredBusch
              last edited by

              This would be the VB to split out the string.

                  Dim telephone As String = PHONE1.Substring(0, 10)
                  Dim extension As String = PHONE1.Substring(10, 4)
              
              JaredBuschJ 1 Reply Last reply Reply Quote 0
              • JaredBuschJ
                JaredBusch @JaredBusch
                last edited by

                ok the automagic code this is a pain in the ass.

                1 Reply Last reply Reply Quote 0
                • garak0410G
                  garak0410
                  last edited by

                  Let me give some of this a try...will report back results...

                  1 Reply Last reply Reply Quote 0
                  • garak0410G
                    garak0410 @JaredBusch
                    last edited by

                    @JaredBusch said:

                    @JaredBusch said:

                    Wait wait wait.. you are using a case statement? where is the rest of this statement...

                    This is what a select case statement is for in VB

                    Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                        ' results.AddPropertyError("<Error-Message>")
                        Dim PHONE1 As String = "12345678901234"
                        Select Case Mid(PHONE1, 0, 10)
                            Case "1234567890"
                                'do something here.
                            Case Else
                                'do something else.
                        End Select
                    End Sub
                    

                    @JaredBusch said:

                    This would be the VB to split out the string.

                        Dim telephone As String = PHONE1.Substring(0, 10)
                        Dim extension As String = PHONE1.Substring(10, 4)
                    

                    OK...I made the variable declarations and forgive me if I don't know how to write code but I've tried various combinations of code and it accepts it but still shows 14 digits in the PHONE1 field...last case I used was:

                    Public Class RM00101Item

                        Dim telephone As String = PHONE1.Substring(0, 10)
                        Dim extension As String = PHONE1.Substring(10, 4)
                    
                        Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                            ' results.AddPropertyError("<Error-Message>")
                            Select Case Mid(PHONE1, 0, 10)
                                Case Is = PHONE1
                                    telephone.Equals(True)
                            End Select
                    
                        End Sub
                    End Class
                    

                    I am sure that is not the correct method but unsure what to do on this LightSwitch / VB language...

                    1 Reply Last reply Reply Quote 0
                    • JaredBuschJ
                      JaredBusch
                      last edited by

                      I have no idea what you are doing without seeing the entire section. try this. This will replace PHONE1 with the first 10 digits of PHONE1. But since this is only a single subrouting, I have no idea what is really going on here.

                      Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                          ' results.AddPropertyError("<Error-Message>")
                          PHONE1 = PHONE1.Substring(0, 10)
                      
                      End Sub
                      
                      garak0410G 1 Reply Last reply Reply Quote 0
                      • garak0410G
                        garak0410 @JaredBusch
                        last edited by

                        @JaredBusch said:

                        I have no idea what you are doing without seeing the entire section. try this. This will replace PHONE1 with the first 10 digits of PHONE1. But since this is only a single subrouting, I have no idea what is really going on here.

                        Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                            ' results.AddPropertyError("<Error-Message>")
                            PHONE1 = PHONE1.Substring(0, 10)
                        
                        End Sub
                        

                        Neither do I...LOL. I do appreciate the help so far.

                        JaredBuschJ 1 Reply Last reply Reply Quote 0
                        • JaredBuschJ
                          JaredBusch @garak0410
                          last edited by

                          @garak0410 said:

                          @JaredBusch said:

                          I have no idea what you are doing without seeing the entire section. try this. This will replace PHONE1 with the first 10 digits of PHONE1. But since this is only a single subrouting, I have no idea what is really going on here.

                          Neither do I...LOL. I do appreciate the help so far.

                          Well I know exactly what is going on in that little snippet I posted. but without seeing more of your code, I have no idea what I am actually affecting..

                          1 Reply Last reply Reply Quote 0
                          • NetworkNerdN
                            NetworkNerd
                            last edited by

                            If I understand correctly, that code snippet is only for a validation that will be run against the Phone1 field. Are you just trying to throw up a validation error on a web page or within an application if a proper phone number is not entered in the PHONE1 field?

                            JaredBuschJ 1 Reply Last reply Reply Quote 0
                            • P
                              Pol @JaredBusch
                              last edited by

                              @JaredBusch said:

                              @garak0410 said:

                              Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                                  ' results.AddPropertyError("<Error-Message>")
                                  Select Left(PHONE1, 10)
                                  End Select
                              End Sub
                              

                              Wait wait wait.. you are using a case statement? where is the rest of this statement...

                              I wonder also. Why using case statement without conditions within? 😄

                              1 Reply Last reply Reply Quote 0
                              • JaredBuschJ
                                JaredBusch @NetworkNerd
                                last edited by

                                @NetworkNerd said:

                                If I understand correctly, that code snippet is only for a validation that will be run against the Phone1 field. Are you just trying to throw up a validation error on a web page or within an application if a proper phone number is not entered in the PHONE1 field?

                                He wants to extract out the first 10 digits of the PHONE1 field and present them to the application / page as the phone number so that the native regional formatting of 10 digit TN handles it correctly.

                                1 Reply Last reply Reply Quote 0
                                • 1 / 1
                                • First post
                                  Last post