Help with SQL Update/Substring

[cwsstins]

Have a CLOB that contains the words "Account Number" in every record. In some records, that phrase is followed by a colon, then the account number. In some records, it's followed by more text.

I need to do a select on the account number in the CLOB so I can use it to update a field on another table. Assuming that I need the SUBSTRING function, but I'm not quite sure how to get what I'm after.

If the words "Account Number" are followed by ": 111111" where 111111 is any number (but not alpha), then it's a match. If "Account Number" is anything else, then it's not a match.

This is what I've got so far:

UPDATE tbl_1
SET fld_1 = 'T'
WHERE custid=
(
SELECT custid FROM tbl_1, tbl_2
WHERE tb1_1.custid like tbl_2.clob 
WHERE EXISTS tbl_2.clob like '%Account Number: '

 

[rac2]

Do you wish to set fld_1 = 'T' in every row in tbl_1 that has an 'Account Number: ' in tbl_2?

Or are you needing to get the account number if it is found and do something with that value in tbl_1?

In the first case and assuming the two tables both have the custid -

UPDATE tbl_1
SET fld_1 = 'T'
WHERE custid IN (
   SELECT custid FROM tbl_1
   JOIN tbl_2 ON tb1_1.custid = tb1_2.custid
   WHERE CAST(tbl_2.clob AS VARCHAR) LIKE '%Account Number: %'

Note the wild card at the beginning and end of the string being matched. Also note that it may be necessary to convert the CLOB to VARCHAR.

Or is the account number in the CLOB the customer id that must be used to JOIN the tables?

 

[cwsstins]

tbl_1 contains:
col_1 does data exist for customer in clob T/F?
col_2 custid
col_3 acctnumber
col_4 customer name *

tbl_2 contains
col_1 customer name *
col_2 clob containing "Account Number" statement

* The problem with customer name is that there is much disparity between the two values. The customer name in tbl_1 is ALLCAPS, with no punctuation. The customer name in tbl_2 is case-sensitive with apostrophes, commas, periods, etc. Tried to do a match with this column, but ran into problems due to the formatting differences.

My objective is to populate tbl_1.col_1 with a 'T' if there is any data for that customer in tbl_2. Is there way to do this with a join and the cast function? I haven't used cast before...

 

[rac2]

Looks like this is a one-time-only data-cleaning project.

You might try adding a column to tbl_2 for custid. Work on filling this with the matching value from tbl_1.custid using the customer name and the acctnumber. Possibly this is what you are trying to do with tbl_1.col_1. Or maybe you have some further steps in mind once that column is given values.

If so, you may wish to consider multiple passes and different ways of matching up rows from the two tables. Do the easy things first and the things that will lead to the most matches. For example, some of the names must be consistent enough to write expressions to match them. There may be two or three patterns that account for 80% of the rows. On each pass update the new column tbl_2.custid
with the value from the matching row in tbl_1.

The account number can be used to obtain matches where it exists. Look for the pattern 'Account Number: ' + tbl_1.acctnumber in tbl_2.clob .

After you get matches for the easy patterns it may be easier to simple view the remaining cases rather than trying to write expressions to match up disparate versions of the same name. The human brain is still the most effective pattern-matcher known.

Partial matches on fragments of the name can reduce the number of cases reviewed manually.

In the end there may be rows in tbl_1 that do not have an identifiable match in tbl_2. Thats the way it goes.

Hope this helps.

Oh, regarding CAST ( expression AS datatype ). No big deal, it is just a function to convert data from one type to another. I mentioned it because I thought maybe you were referring to datatype with the term clob. It reminded me of BLOB, binary large object. It seems I ran into the need to do this with TEXT datatypes once. But it appears that clob is merely the name of a column, so the CAST may be irrelevant.

 

[cwsstins]

Well, this turned into a project that was taking more time than I could really commit, so we're going another route. Someone is going to compile and provide me with a list of account numbers, which will obviously simplify the SQL statement needed to accomplish this task. Thanks for your feedback rac2...and for the info on CAST.