Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-22 : 10:29:52
|
| Brian writes "I need to write a t-sql procedure that will loop through all of the records in a table, read a text field in each one, read the text field line by line putting the line into a varchar2, look for a text string, if it finds it look for three other text string and then insert a record into a table. Mostly I am strugling with how to use ReadText to pull out one line at a time and place it in a varchar2. How can I pull out one line at a time? Each line inside the test field ends with a carraige return, the line lengths vary. The solution needs to work with Sql Server 7.0Thanks in advance." |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-22 : 11:46:05
|
| I'm semi confused by how you stated this, but... Instead of reading it line by line can you use the contains statement to search the entire text field for the string your looking for? |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-22 : 12:05:40
|
| SQL Server is not designed for this type of iterative process and the string manipulation functionality is sparse . . . however, if you must . . .You will need to use charindex(char(13),<textcolumn>,@last_charindex) to find the carriage returns. Then call READTEXT with the appropriate offest based on what you find for a charindex. Its gonna be a PIA.<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 12:13:31
|
| I agree with everyone on this, doing it line-by-line is not a good idea, and I don't think you need it...I don't think you even need READTEXT, if you just need to identify the rows that have those search conditions:SELECT textCol FROM myTableWHERE PatIndex('%' + @search1 + '%', textCol) > 0 AND(PatIndex('%' + @search2 + '%', textCol) > 0 ORPatIndex('%' + @search3 + '%', textCol) > 0 ORPatIndex('%' + @search4 + '%', textCol) > 0 )This will find the rows that have the first search argument and at least of the other 3. It ignores line breaks completely (I confess I can't see what difference line breaks would make...you'll have to explain that in more detail)As far as the INSERT operation, you'll have to spell out what you're trying to do with the whole procedure. Not the mechanics of it, just the basic points: you've got some search terms, you want to search this column for them, there are some conditions, and when you find them then do such-and-such. Err on the side of giving too much information, don't leave anything out because it doesn't seem important.Edited by - robvolk on 05/22/2002 12:14:18 |
|
|
|
gandolf989
Starting Member
5 Posts |
Posted - 2002-05-23 : 17:25:04
|
| Thanks for all of the input.Each text field contains the following data"OTHERVALUE=SOMETHING" "X=Y" "ETC=ETC""OTHERVALUE=SOMETHING" "X=Y" "ETC=ETC""OTHERVALUE=SOMETHING" "X=Y" "ETC=ETC""OTHERVALUE=SOMETHING" "X=Y" "ETC=ETC""OTHERVALUE=SOMETHING" "X=Y" "ETC=ETC""MYUDF=VALUE" "BAND=FORGROUND" "VISIBLE=Y" "TABORDER=0"So I needed to look through each line in each text field and decide if it was what I was looking for. I know I can do it in Oracle. But it seems like it can't be done in T-SQL. Anyway I'm off the hook the Power Builder programmers are going to do it.Thanks again.Brian |
|
|
|
|
|
|
|
|
Using ReadText