Have a look at PATINDEX....




>They sent me an Excel file with transactional data. I have to check if those transactions are in our master database.
>The master DB has over 41 million records.
>
>The key fields I need to look for are account number and transaction datetime. Both are char columns. The trandate is of the type:
>YYMMDDHHMMSS - Char (12)
>
>The Excel data they sent is incomplete as it goes to HHMM and no seconds. I already converted the data manually from Excel to CSV then did a lot of text manipulations to get it into the proper format (there are many malformed other fields). Then I got it into a DBF and I have a handy routine I always use for this type of check that reads this DBF and checks against SQL Server for the existence of each record and writes how many hits I got. In theory should be 1, but sometimes we may have 0 hits or sometimes more than 1 (duplicates), both are error conditions and that is exactly what we want to know.
>
>The problem is this time they did not include the SS (seconds) part. Is there an easy way to search? In VFP it's easy just searching the first 10 chars instead of all 12. In SQL Server I do not have an index (or column) for only 10 chars, just all 12 plus another index on the account column. Assuming I can find it, it will be very slow with no index.
>
>Is there an equivalent to VFP to search for a 10-char string into a 12-char column and still find a match?