First off, this is my first time posting on this forum. I've got a database with a long list of strings, what I'd like to do is find how many of these strings contain other strings in the same table, and if they are sorted in descending order, what's it's index within that sub table?
For instance, if I have the strings a, await, wait, waiting, walk then the index for wait should be 2 (or 1 if we start counting from 0).
I was able to count how many occurrences there were (in this case 3).
Each name in the table is unique.
The query only needs to be run once every few months and has a list of about 2000 string.
I thought up something that I've done before using SQL and Excel. I used the following query:
answered Oct 12 '09 at 02:17 AM
This answer requires SQL Server 2005 or above. I'm not sure of the exact output you were looking forward, but I'm sure you can tweak that having this solution.
I didn't know the structure of your table so I can only present an example solution and not the exact solution with your table name and field names. Skip down to the SELECT portion to see how this works.
--create a table to hold the terms to search
--add a unique key that is clustered (for default ordering purposes)
--add some values
--view the list of terms
--view the list of terms with the accompanying terms they are found in, the count and the
--clean up after test
answered Oct 15 '09 at 07:35 PM