Querying middle 3 numbers

[ferrisj]

i have a list of phone numbers and i want to just display the exchange ie if (555) 412-5555 is the number i just want to display 412. how do i trim off teh first 6 charcters and the last 5

 

[earthandfire]

Assuming the numbers are always in that exact format and that your field is called phonenum - you could use something like:

mid(phonenum,instr(phonenum," ") + 1, 3)

Hope this helps.

 

[ferrisj]

and if i just wanted to cut off the last 4 digits, how would i do that

 

[earthandfire]

... or even simpler:

mid(phonenum,7,3)


Hope this helps

 

[earthandfire]

left(phonenum(len(phonenum) - 5))

or

left(phonenum,9)

assuming you don't want the "-"


Hope this helps.

 

[MichaelRed]

Although some what more challenging to set up, a lot safer method would be to strip all the non numeric chars from the string, check the length of the string to assure that there are JUST the 10 in the US and then pick out the subset.

You may find instances where there are only seven chars (indicating no area code) or a variable number of others.

This last bit can easily be daunting, as the "extras" might easily be a country code or -just as easily- an extension. No advice currently available from me to differentiate between these, and I'm reasonable sure from your previous responses that you are not into that anyway.




MichaelRed