Post reply

Trim first two characters in field of variable length

  • February 18, 2009 at 1:30 pm

    #200099

    Hello:

    I have a PhoneNumber field in which the entries begin with either W: for work or H: for home (e.g., H:607-544-3451). I want to display the field in a report without the prefix (e.g., 607-544-3451), but the problem I'm having is that the phone numbers themselves are of variable length. For example, one phone number could be H:607-544-3451, while another could be W:(607) 831-8273. (This isn't my fault, I swear--I inherited the database.)

    At first, I tried to use RIGHT(Fields!PhoneNumber.Value, 12), but there won't always be 12 characters--if the area code is in parentheses, it'll cut off part of the number.

    Does anyone know how to trim the first two characters if you don't know how many characters you'll need to display?

    Thanks in advance! 😀

  • February 18, 2009 at 1:35 pm

    #946290

    lk (2/18/2009)

    Hello:

    I have a PhoneNumber field in which the entries begin with either W: for work or H: for home (e.g., H:607-544-3451). I want to display the field in a report without the prefix (e.g., 607-544-3451), but the problem I'm having is that the phone numbers themselves are of variable length. For example, one phone number could be H:607-544-3451, while another could be W:(607) 831-8273. (This isn't my fault, I swear--I inherited the database.)

    At first, I tried to use RIGHT(Fields!PhoneNumber.Value, 12), but there won't always be 12 characters--if the area code is in parentheses, it'll cut off part of the number.

    Does anyone know how to trim the first two characters if you don't know how many characters you'll need to display?

    Thanks in advance! 😀

    SELECT STUFF('H:607-544-3451',1,2,'')

    Or More generally:

    SELECT STUFF(YourColumn,1,2,'')

    FROM YourTable


    * Noel

  • February 18, 2009 at 1:42 pm

    #946299

    Hi,

    Will this work for you??

    SELECT RIGHT('PRAKASH',LEN('PRAKASH')-2)

    Thanks

    ---------------------------------------------------------------------------------

  • February 18, 2009 at 2:12 pm

    #946331

    How about:

    selectSUBSTRING ( PhoneNo, 3, LEN (PhoneNo) )

  • February 19, 2009 at 12:37 pm

    #947042

    Shahryar Ali (2/18/2009)

    How about:

    selectSUBSTRING ( PhoneNo, 3, LEN (PhoneNo) )

    I would def. go with this as well.

  • February 19, 2009 at 2:35 pm

    #947168

    Hello, all:

    Unfortunately I'm an idiot and I realized through trying and failing to use your suggestions that I need to write this in VBA, not in SQL. I'm really sorry to have wasted everyone's time...I guess I'll slink off and post on the VBA board now. :ermm:

    Sorry!

  • February 19, 2009 at 3:10 pm

    #947189

    lk (2/19/2009)

    Hello, all:

    Unfortunately I'm an idiot and I realized through trying and failing to use your suggestions that I need to write this in VBA, not in SQL. I'm really sorry to have wasted everyone's time...I guess I'll slink off and post on the VBA board now. :ermm:

    Sorry!

    [font="Verdana"]Well, in that case, try looking up the VB Mid() function. :D[/font]

  • February 19, 2009 at 4:31 pm

    #947260

    = MID(Fields!PhoneNumber.Value, 3)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply