[RESOLVED] Help with string functions

[Daniel Stead]

Does anyone know of a quick fix to this problem?

I have a column in my table that contains various phone numbers. However to keep these unique any phone number that is a duplicate has been assigned a ~ and the number referring to the duplicate occurrence. So I am left with data as shown below

01234567890
01234567890~1
01234567890~2
01345678901
01345678901~1
01345678901~2
01345678901~3
etc....

What I want to do is ignore the ~ and the number on the end of the phone number like so:

Left$(PhoneNumber, 11)

The only problem being that some of my phone numbers are 10 digits and some are 11 digits. Is there any function that will look at a string and get everything to the left of it i.e. everything left of the character "~"

Left$(PhoneNumber, "~")

Many Thanks

Daniel Stead

[Hack]

What are you using for a front end?

VB6 and VB.NET could parse that using the Split Function.

[si_the_geek]

And if you want to do it in an SQL statement (which would be more complicated), which database system are you using?

[Daniel Stead]

Im using micorsoft access 2003 with vb version 6.

Ideally i would like to do this using sql but would welcome either option as i have tired many different options with no success.

[Hack]

With VB6

Dim strPhoneNo() As String
strPhoneNo = "01234567890~2"
strPhoneNo = Split(strPhoneNo, "~")
Msgbox strPhoneNo(0)

[salvelinus]

Never mind.

[Daniel Stead]

Thanks for the solution but does anyone know how i can do this in sql or if it is possible in sql? It would just make my life alot easier.

[salvelinus]

You could use strPhone(0) in SQL. I was thinking above of using Replace() to get rid of the ~, but realized you'd still have the extra digit. I suppose you could combine Left() & Replace() in SQL.
Why do some phone numbers have 11 digits?

[si_the_geek]

Based on a little test, I think this should work:

SELECT PhoneNumber, IIf(Instr(PhoneNumber,"~")=0,PhoneNumber,Left(PhoneNumber,Instr(PhoneNumber,"~")-1))
FROM tablename;

However, I can't help feeling that having the data like that in the first place is a really bad idea - it could easily have been avoided by using an Autonumber field.


edit: corrected typo (for some reason had written "s" instead of "~" )

[Daniel Stead]

Thanks for the reply and i totally agree that it is a bad way to store data but i can only work with what i am given.

I can see how the code would work that you have supplied as if the string has a ~ it finds the poistion of this and uses this to get everything -1 of that postion.

I am sturggling however to work out the first part of the code i.e.

Instr(PhoneNumber, "s") = 0

What does this do as my instinct would be search for the value s in the string?

Many thanks for your help

[Daniel Stead]

Never mind i figured it out and it works perfectly thanks for your help

Dan Stead

[si_the_geek]

Just for clarification, Instr does search for a string within another - it returns the position that it is found at, or 0 if it is not found.


As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

[MaximilianMayrhofer]

I know that it's there because it will always return true, therefore executing the command that you really want, but I don't understand why it's necessary to construct a self-fulfilling IIf statement. Can you explain si?

[si_the_geek]

It was actually a typo, instead of "s" it should have been "~" (based on his latest post, I'm assuming Dan noticed and corrected it!)