Discussion Locked

SQL SERVER PROBLEM - SEARCH FOR STRING

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a SQL server 7.0 problems and I need a script that can go through a column in a table to find specific piece of data and update another table with that data

TABLEA may contain multiple entries for the Notes Field. So for each entry I want to
Search each of the entries
TABLEA (AccountNo, Notes)

*** JULIES *** July 4, 2001 at 11:27am
Siva faxed required delivery schedule. first delivery 23rd July for 24 of

*** JULIES *** June 22, 2001 at 4:22pm
Siva called me back to tell me he needs 4 of the same spec as 458882 now and that they will be

The NOTES field has multiple entries at different date and time.

From the information in the "Notes" . Extract the details there and place the relevant details into "history" TABLEB using the Date separator as a record separator, the first 30 characters as the "Reference" and the whole note as the "Notes" data ie records that look like...

*** JULIES *** July 4, 2001 at 11:27am
Siva faxed required delivery schedule. first delivery 23rd July for 24 of

Should end up as history items with the following format...

TABLEB (AccountNo Date, User, Reference, Notes)

DATE: July 4, 2001 at 11:27am
User: *** JULIES ***
REFERENCE: Siva faxed required delivery (30 char of the notes field)
NOTES: Siva faxed required delivery schedule. first delivery 23rd July for 24 of

I really hope this make sense.

1 reply

by andrew

Through judicious use of the SQL string functions, you can parse the Notes field into chunks:

select
substring(Notes,1,patindex('% *** %',Notes)+3) [User],
substring(Notes,patindex('% *** %',Notes)+5,patindex('%[ap]m %',Notes)+3-(patindex('% *** %',Notes)+5)) [Date],
right(Notes,datalength(Notes)-patindex('%[ap]m %',Notes)-2)
from TableA

Then INSERT into TableB.

This is no fun, is slow and error prone. You might consider breaking up TableA into more discrete fields.

Hope thishleps.

This discussion is closed to new replies.