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

search and separate a string within another string?

P: n/a
Hello, I have 4,4 million rows of data in one column. I want to search
for a string in each line. There are random amount of characters
before the string I want to find. The string starts with the letter
"V" and I also know that after the letter V comes seven digits or
letters which I want to find also. After that string there are random
amount of characters. What kind of query I should create to find all
those strings within my data? I want also to separate them to another
column. What should I do?

Thanks for your help beforehand.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try using something like this

strKeyword = "string you want to find"

select *
from tblYourTable
where yourField like '*" & strKeyword & "*'"

That will only work if your looking for a known string, which now i think
more you dont want. Try making a function such as below and then using it in
your sql ;

function containsVthingy(strInput as string) as boolean
'do VB code to find the string you want
'lookup inStr
end function

select *
from tblYourTable
where containsVthingy(yourField) = true

if you wanted to save the Vthingy in another field you could just use an
update query, the main thing is you can use ur vb functions in queries.

Thats not accurate code but it should give you the idea and I hope Im not
leading you down the wrong path cause im no guru.
Hope it helps
John Sheppard

"harhaiko" <ha******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...
Hello, I have 4,4 million rows of data in one column. I want to search
for a string in each line. There are random amount of characters
before the string I want to find. The string starts with the letter
"V" and I also know that after the letter V comes seven digits or
letters which I want to find also. After that string there are random
amount of characters. What kind of query I should create to find all
those strings within my data? I want also to separate them to another
column. What should I do?

Thanks for your help beforehand.

Nov 12 '05 #2

P: n/a
So I guess doing it manually is out of the question? (Oh, just
kidding!)

Okay, the string starts at the first "V" Use InStr() to find it.
Something like instr(1,txtSearch,txtSearchIn,vbTextCompare)

umm... Mid$(txtSearchIn,instr(1,txtSearchFor,txtSearchIn, vbTextCompare),8)

or something pretty close to that... HTH
Nov 12 '05 #3

P: n/a
Use LIKE in your selection criteria.

SELECT tblPart.Description
FROM tblPart
WHERE (((tblPart.Description) Like "*V???????*"));

* matches anything including NULL
? matches any character

"harhaiko" <ha******@hotmail.com> wrote in message
news:fe**************************@posting.google.c om...
Hello, I have 4,4 million rows of data in one column. I want to search
for a string in each line. There are random amount of characters
before the string I want to find. The string starts with the letter
"V" and I also know that after the letter V comes seven digits or
letters which I want to find also. After that string there are random
amount of characters. What kind of query I should create to find all
those strings within my data? I want also to separate them to another
column. What should I do?

Thanks for your help beforehand.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.