SQL Question - Removing Part Of A Field

garak0410

I've got a SQL table I've imported into Visual Studio LightSwitch 2013 that came from Microsoft Dynamics Great Plains 2010. Everything looks great except for Phone Numbers...they are all in a 14 digit format (last for digits for extension number) and LightSwitch cannot parse this with its own Phone Number data type.

I was wondering if there was a SQL command where I can run against the phone number columns (Phone1, Phone2, Phone3 and Fax) that would remove the last for digits. Currently they show as 00000000000000 . I am thinking if I can remove those last 4, LightSwitch will parse correctly.

Thanks...
Brian

scottalanmiller

"For digits"?

Lol

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.

garak0410

@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.

JaredBusch

@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

JaredBusch

@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...

JaredBusch

This post is deleted!

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

JaredBusch

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)

JaredBusch

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

garak0410

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

garak0410

@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...

JaredBusch

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

garak0410

@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.

JaredBusch

@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..

NetworkNerd

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?

Pol

@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?

JaredBusch

@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.