By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Running a query to extract substring

P: 2
Hey thr!

I am in a fix please help me out.
Thing is i have extracted IP address now i want the substrings in parts. e.g.
if Ip address is 121.43.45.56 i want 121 , 43, 45 and 56 separately. how shud i go about it.

I tried for substring_index but wasnt successful.

waiting fr reponse....
thanks
Sep 26 '06 #1
Share this Question
Share on Google+
2 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi
Create module and add this function to it!

Expand|Select|Wrap|Line Numbers
  1. Function get_word(my_sentence, sep, word_N) As String
  2. Dim Result As Integer
  3. Dim my_str
  4. Dim my_word
  5. Result = 1
  6. 'Stop
  7. Do While InStr(1, my_str, sep) > 0
  8.     If Result = word_N Then
  9.         my_word = Mid(my_str, 1, InStr(1, my_str, sep) - 1)
  10.         get_word = my_word
  11.         Exit Function
  12.     End If
  13. Result = Result + 1
  14. my_str = Mid(my_str, InStr(1, my_str, sep) + 1, Len(my_str) - 1)
  15. Loop
  16.     If Result = word_N Then
  17.         my_word = my_str
  18.         get_word = my_word
  19.         Exit Function
  20.     End If
  21. get_word = ""
  22. End Function
  23.  
In your query

First:get_word([IP], ".", 1)
Second:get_word([IP], ".", 2)
Tird:get_word([IP], ".", 3)
And so on...

Have a nice day!

:)

Hey thr!

I am in a fix please help me out.
Thing is i have extracted IP address now i want the substrings in parts. e.g.
if Ip address is 121.43.45.56 i want 121 , 43, 45 and 56 separately. how shud i go about it.

I tried for substring_index but wasnt successful.

waiting fr reponse....
thanks
Sep 26 '06 #2

P: 2
hey

thanks a lot for tht!


Hi
Create module and add this function to it!

Expand|Select|Wrap|Line Numbers
  1. Function get_word(my_sentence, sep, word_N) As String
  2. Dim Result As Integer
  3. Dim my_str
  4. Dim my_word
  5. Result = 1
  6. 'Stop
  7. Do While InStr(1, my_str, sep) > 0
  8.     If Result = word_N Then
  9.         my_word = Mid(my_str, 1, InStr(1, my_str, sep) - 1)
  10.         get_word = my_word
  11.         Exit Function
  12.     End If
  13. Result = Result + 1
  14. my_str = Mid(my_str, InStr(1, my_str, sep) + 1, Len(my_str) - 1)
  15. Loop
  16.     If Result = word_N Then
  17.         my_word = my_str
  18.         get_word = my_word
  19.         Exit Function
  20.     End If
  21. get_word = ""
  22. End Function
  23.  
In your query

First:get_word([IP], ".", 1)
Second:get_word([IP], ".", 2)
Tird:get_word([IP], ".", 3)
And so on...

Have a nice day!

:)
Sep 26 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.