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.
    • NetworkNerdN
      NetworkNerd
      last edited by NetworkNerd

      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.

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