Instr problem/query

[karolmcauley]

Hello,

I have names and telephone numbers of various people stored in one field in an oracle db. If there is more than one name and number in the field the names and phone numbers will be separated by "~~", which vbscript substitutes for carriage returns in my script.

I have an instr Function in this script that searches a string this character, e.g.

dim posmarker as string
dim strContacts as string


strContacts = rsTemp("contact_telephone")
posmarker=instr(1, strContacts,"~~")

This does tell me the position when there is a second name and telephone number. Now if i want to exclude all names and numbers that follow "~~" in the oracle db field how would i go about it?

anybody any ideas?

Many thanks

[phrozeman]

use the split function on ~~

list1.additem split(strContacts, "~~")(0)

[karolmcauley]

Hello,

Thanks for that phroze man.

Furthermore, if "~" = carriage return and some users have entered names and telephone numbers in one oracle db field like:

myname1~mynumber1~~myname2~mynumber2

displaying the page like:

myname1
mynumber 1

myname2
mynumber2

Bearing in mind my original query (remove all names and numbers after "~~" ) how could i remove the "~" in between the first name and number as this is still displayed as myname1~mynumber1 on the page. Would the replace function be useful?

Many thanks

[Spajeoly]

As a matter of fact yyyy,, No! Well here is how I would do it..

StrContacts = Mid(StrContacts, 1, InStr(1, StrContacts, "~")-1)

That should do it, I hope, I always use the Mid & InStr functions together like that.

[karolmcauley]

Spajeoly

Your code above will eliminate the first telephone number if the user has entered:

myname1~mynumber1~~myname2~mynumber2

I want to return myname1 and mynumber 1 without the "~" separating the two. Yor code only returns myname and that's it.

Any ideas?

Cheers

[Spajeoly]

Oh ok, I misunderstoodulated your request..... My bad, hell yes use the replace function!!!

strContacts = Replace(strContacts, "~", "")

That should do it, now hopping off into the sunset I go, weeeee....

<<Puts bullet in head>>

[karolmcauley]

Hello,

Sorry for all this confusion Spajeoly but you still misunderstand me.

strContacts = Replace(strContacts, "~", "") will give me all the names in the db field as well as all the numbers simply replacing "~" with "".

I need to concatenate :
Replace(strContacts, "~", "") with
split(strContacts, "~~")(0)
so that i only get the first contact name and telephone number of any field whilst at the same time replacing "~" with " ".

i have tried giving each a variable name and then ampersand them together but no joy....any ideas?

[Spajeoly]

Hahaaaaaaaaaaa! Ok, so I am retarded, so what!!!

Lets try this....

StrContacts = Mid(StrContacts, 1, InStr(1, StrContacts, "~~")-2)

If this doesn't work give me a slight example of your code so I can actualy open up VB and try something with it.

We will get this, I swear!

[karolmcauley]

Unfortunately still no joy,

Your last suggestion basically does the same as :

StrContacts = Mid(StrContacts, 1, InStr(1, StrContacts, "~~")-1) that you posted earlier just not returning the last character of the field before "~~".

My page is actually within an asp page which utilises vb script which connects to an oracle 8i databae successfully returning several fields one of which is telephone names and numbers (stored in one field). The db field can hold unlimited names and phone numbers (up to 2000 characters). So instead of displaying all names and numbers in a big long line i want to only return the first name and number. We use "~" to represent a carriage return in the db field as you cannot do it in there.
So users can put in name1~number1~~name2~number2~~name3~number3, etc etc

My asp page checks for "~" to break the name and number up accordingly. Now i only want to return name and number 1 with out the "~" in between it.

Any more ideas?

[Spajeoly]

DUh, I forgot to post a part of the ocde, sorry I am doing 200 things at once here lol, check this...

StrContacts = Mid(StrContacts, 1, InStr(1, StrContacts, "~~")
StrContacts = Replace(StrContacts, "~", "")

Let me know if that does something, if need be just take my example and modify it, you can adjust it to stop InStr basicly anywhere as long as the character is always the same in the strings where you want it to stop... Then i use the replace function to get rid of the unwanted ~ in there...

I am using the instr function assuming it should stop on the double tilda's ~~, are you saying it is stopping on the first one?