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

    SQL Question - Removing Part Of A Field

    Scheduled Pinned Locked Moved IT Discussion
    lightswitchsql serversqlt-sql
    18 Posts 5 Posters 4.0k Views
    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.
    • garak0410G
      garak0410 @NetworkNerd
      last edited by

      @NetworkNerd said:

      How about something like select left(phone1,10), left(phone2,10),... from table....?

      That should pick off only the 10 leftmost characters and trim off the last 4.

      I've tried that and perhaps it is just trying to find the exact place for this command. In LightSwitch, you have an option under properties for the PHONE1, PHONE2, etc. for CUSTOM VALIDATION and it brings up a code window. In this window, I had added a similar command as you suggested:

      Namespace LightSwitchApplication

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

      End Namespace

      It is correct syntax for LightSwitch (Visual Basic), but it still doesn't shave off the last 4 digits.

      I know LightSwitch isn't popular but it is fairly easy for me to whip up easy applications. Just need to get it just right to work.

      1 Reply Last reply Reply Quote 0
      • 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