finding a word, submitting part of it

Dear All

Does anyone know any syntax that will allow me to find entries in a atable when only part of a word is entered. I have a table that has been full txt indexed at the moment I am using the CONTAINS keyword to search fields. This works fine 'select * from tblX where contains(colX, 'gill')', gives me back what i want, but what if i type this 'select * from tblX where contains(colX, 'ill')' it doesn't find anything. What i want is a command that will search all the words in a table column. Does anyone know of a command that will do this?

ThanksI got into the CONTAINS (T-SQL) syntax, and I found only prefix_terms like 'ill*', but I didn't found a solution for a '*ill' condition. Using the LIKE operator isn't an option either. I hope that other guys can help you. :(|||Well, LIKE is an option, just not a very efficient one. But, it's there for just this type of search.

SELECT * FROM tblX WHERE colX LIKE '%ill'

You can also replace the wildcard % with:

_ looks for any single char

[] looks for any chars or range of chars you specify ([a-q], [abcd], [1-9], etc)

[^] same as above, but exclusion chars ([^a-f] means anything but a through f)|||Another option is to use CHARINDEX:

where charindex('ill', colx) > 0

I don't know how this compares to LIKE for speed, but I suspect it would be faster because it has less functionality (no wildcards) and thus may have less processing overhead.

blindman|||I think you are right. Though, any time you ask SQL to do string processing, you are going to take a hit. They seem to have build in several levels of functionality here, getting progressively deeper as you need them. Can't think of why they would do that vs a sigle flexible function, unless the performance would dive.

Find my Syntax Error!! (Because i cant see it)

Im getting an syntax error in the insert into statement is what my error message says, but i cant see it. So if you could take a look and maybe find the problem it would greatly help.

---

With addDonorReciepts
.CommandText = "insert into FoodDonations (Company, Phone, Contact Name, Street Address, Suit, City, General Location, Donor Date, Donor Time, Bakery Donated, Meat Donated, Fruit Donated, Dairy Donated, Vegetables Donated, Prepared Donated, Beverages Donated, Non-Perish Donated, Non-Food Donated, Calc1 Total, Total Donations, Receipt Number) values ('" & oneRowDon.Item(1) & "', " & oneRowDon.Item(2) & ", '" & oneRowDon.Item(3) & "', '" & oneRowDon.Item(4) & "', " & oneRowDon.Item(5) & ", '" & oneRowDon.Item(6) & "', '" & oneRowDon.Item(7) & "', " & oneRowDon.Item(8) & ", " & oneRowDon.Item(8) & ", " & oneRowDon.Item(11) & ", " & oneRowDon.Item(13) & ", " & oneRowDon.Item(14) & ", " & oneRowDon.Item(12) & ", " & oneRowDon.Item(15) & ", " & oneRowDon.Item(16) & ", " & oneRowDon.Item(19) & ", " & oneRowDon.Item(20) & ", " & oneRowDon.Item(21) & ", " & oneRowDon.Item(22) & ", " & oneRowDon.Item(22) & ", " & rNumber & ")"
.Connection = FHDB
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With

---

Item(1) - String
Item(2) - Int
Item(3) - String
Item(4) - String
Item(5) - int
Item(6) - string
Item(7) - string
Item(8) - Dunno its a DateTime thing
Item(11) - int
Item(13) - int
Item(14) - int
Item(12) - int
Item(15) - int
Item(16) - int
Item(19) - int
Item(20) - int
Item(21) - int
Item(22) - int
rNumber - int

You need to delimit all column names that have spaces. Change Contact Name to [Contact Name], etc.

A good way to debug a SQL statement that you generate is to look at the commandtext string either from debugger or by printing it out and inspecting from SQL Editor in SQL Management Studio where you get basic color coding help.

Hope this helps.

|||Aha... Thanks :)
also some of those ints were not really ints so that caused a few problems