Hello,

I am writing a stored procedure to compare values within a File Name stored in one table to the stored values in another.

Here is an example of the filename:
TESTCOMPANY_ABCD_23_NEW_SUBMISSION.txt.

The filename along with a timestamp is stored in a table named SubmissionManagement. Then, the corresponding records for this file are inserted into another table called Submission which stores each record in the file. Then I need to compare parts of the filename in the SubmissionManagement table to corresponding records in the Submission table. For example, if the filename column in the SubmissionManagement table has the value 23 then the CompanyID field in corresponding records for the Submission table should have the value 23 for all records. If any records do not have this value, then I update the FileLoadStatus flag column in the SubmissionManagement table to 4 (Failed Validation). It would also be nice to return a record count of the failed records, but for now it can just be an all or nothing deal.

I have been searching for examples on this and have seen some brief examples using PATINDEX and SUBSTRING but nothing that actually parses out parts of a filename and compares those values to values in another table, which I assume would involve a loop of some kind. Any help would be greatly appreciated.

Thanks,
Bruce

This can be a little confusing.

First find the location of the second '_'.
then find the location of the third '_'.
Subtract the second from the third to get the length of the number you are looking for.

Then take the substring starting from the second '_' for the length of the number.


declare @test varchar(200)
set @test='TESTCOMPANY_ABCD_23_NEW_SUBMISSION.txt'

--Find second '_'
select charindex('_',@test,charindex('_',@test)+1)+1
--Find third '_'
select charindex('_',@test,charindex('_',@test,charindex('_',@test)+1)+1)

--Substring starting at the second '_' and going for
--the length of the third minus the second.
select substring(@test,charindex('_',@test,charindex('_',@test)+1)+1,
(charindex('_',@test,charindex('_',@test,charindex('_',@test)+1)+1) -
(charindex('_',@test,charindex('_',@test)+1)+1))

Simi

Hi Simi,

OK, I think I'm tracking this. Let me give it a whirl and I'll let you know how it goes. Thank you very much for taking the time out to reply.

Bruce

Hi Simi,

I added a missing parentheis on the end and it worked! Fantastic! So, can you show me an example of how I would apply this string parsing logic to records as I loop through them?

Thanks,
Bruce

As I understand it you need that number to determine the number of records in anouther table?

So, you said looping thru so I guess you have a cursor? or doing this from anouther language. But then you would use that number to count the number of records in the other table and make sure they match?

Not sure if I followed all your example. You might want to supply examples and expected results.

Simi

Actually, I realized that all I need to do at this point is simply loop through values in another table and compare thos, so the heavy lifting ahs been done. It works a treat, thanks again for the assist!