Searching text
-
How do you search a text column to return a user name in the middle of the text and return 50 characters to the right of the name using query analyzer. Each text field holds about 40k and the name can appear more than once in a row...and full text search is not installed...
Thanks
-
Dear Qreqtm
i read your question, but sorry don't get the clear view of your problem
can u elebroate your question with example...
-
For example, everytime a user logs into our applilcation and opens a transaction it writes a entry to a text field called Notes in a table called Transactions. ex.
Our manager ask me to find out when this user has viewed transactions. This is the only column that writes the users name and date that the user viewed the transaction. My dilema is I need to search this text/image column for a user for auditing purposes but in each row there is about 60 entries, I need to get the users name and date; ex.
Notes
row 1
(Dave Markus June 16 2005) no changes were made...
(Glen stevenson Aug 19 2006) Transaction adjusted...
(Dave Markus Sept 03 2006) Reviewed transaction no changes were made...
(John Mentos Oct 06 2006) Made payment to Bank, 2 payments still outstanding...etc
row 2
(Dave Markus June 12 2005) no changes were made...
(Glen stevenson Aug 19 2006) Transaction adjusted...
(Dave Markus Sept 17 2006) Reviewed transaction no changes were made...
(John Mentos Oct 06 2006) Made payment to Bank, 2 payments still outstanding...etc
When I run the following;
Select notes from transactions
where notes like '%Dave Markus%'
I get all rows that Dave Markus has access along with all the other notes I don't want? So My question is how do I return only the name and the date in the text field? EX.
(Dave Markus June 16 2006)
(Dave Markus Sept 03 2006)
(Dave Markus June 12 2005)
(Dave Markus Sept 17 2006)
-
If the parentheses are only used around the names and dates in the text field, your solution would involve a stored procedure that uses a cursor to loop through the required rows ( filtered by '%username%' ). Inside
this cursor you'll need a loop that follows this logic:
1. Find CHARINDEX of Nth exisitence of '(' => A
2. Find CHARINDEX of Nth existence of ')' => B
3. Use SUBSTRING (NotesCol, CHARINDEX(A), (CHARINDEX(B) - 1) - CHARINDEX(A)) to find username and date.
4. If username is required append the substring portion to temp table? or other structure.
5. Increment N and back to stage 1
If I had time I'd write out the T-SQL, but sadly you've caught me at a busy time!!
Hope this helps!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply