0

I need to convert the below SQL statement into Regular Expression. 

CASE WHEN TypeCode >= 400 
      AND TypeCode < 700 
     THEN Amt * -1 
     ELSE Amt 
 END

Background: I am putting a bank transactions file (BAI2 file) in to a system for transactional matching (matching bank transactions to GL transactions). In order to get these transactions to match, the fields to match on have to be exactly the same. However, in the GL a $500 check may be input as -500 (because the company's cash account is being reduced by $500 for a utility bill), but BAI files store all amounts as positive values. I need to use the transaction type code from the bank to identify whether an amount should be a debit or a credit (in reference to the GL).

I have SQL developers that can do this using SQL, but this tool I'm using to do the BAI data manipulation requires the logic to be input as Regular Expression.

Can anyone assist in applying the appropriate signage (positive or negative) to these amounts for bank transactions? Can this even be done? I'm a new poster so please bear with any ignorance and let me know if I can provide further details/information.

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
CluelessJeremy
  • 1
  • 1
  • 2
    regex is used to match patterns, not modify values. What exactly are you trying to convert in that statement? – default Mar 08 '16 at 23:50
  • 1
    What is your SQL RDBMs? – Jorge Campos Mar 08 '16 at 23:54
  • I'm trying to take a transaction with a transaction code (TypeCode) between 399-700 and convert the amount (Amt) to a negative value. I'm not necessarily matching patterns; more modifying data as you said. Can this even be done with regex? – CluelessJeremy Mar 09 '16 at 18:36
  • Sorry Jorge - I don't understand RDBMs? Please excuse the ignorance; i'm by now means a SQL developer and have very little technical background. – CluelessJeremy Mar 09 '16 at 18:37
  • Which DBMS are you using? Postgres? Oracle? DB2? SQL Server? –  Jun 24 '16 at 13:06

2 Answers2

0

Maybe try this:

^[4-6][0-9]{2}$

This will look for numbers between 400-699 inclusive.

EDIT: Changed \d to [0-9] as \d may include weird characters as well.

kaisquared
  • 406
  • 4
  • 10
  • wouldn't `\d\d` be shorter? – default Mar 08 '16 at 23:51
  • Actually I think ^^[4-6][0-9]{2} would be better as \d is too general. Answer edited. – kaisquared Mar 08 '16 at 23:52
  • *\d is short for [0-9]* that I know, so I'm not sure about the "too general". Could you elaborate? – default Mar 08 '16 at 23:54
  • 1
    But that will also match `712345` (more than 3 digits). I think you should write `^[4-6][0-9][0-9]$` – SQL Police Mar 08 '16 at 23:56
  • 1
    http://stackoverflow.com/questions/890686/should-i-use-d-or-0-9-to-match-digits-in-a-perl-regex – kaisquared Mar 08 '16 at 23:56
  • @SQL george I've edited my answer to take your correction into account. – kaisquared Mar 08 '16 at 23:58
  • Thank you all so much for your responses and desire to help! I tried inputting ^[4-6][0-9]{2}$ in my tool and see no results (which is indicative that it didn't work). Still very much interested in determining if/how I can perform this data manipulation with regex and am very welcome/appreciative of everyone's help! – CluelessJeremy Mar 09 '16 at 18:41
  • Maybe try SQL george's suggestion i.e. ^[4-6][0-9][0-9]. {2} might not be accepted syntax in your tool. – kaisquared Mar 09 '16 at 20:22
  • I did try this and was able to get some results, but not what is needed. This (appears to have) took all Amounts in which the first 3 numeric digits are between 400 and 700 and then populated my new field (converted amount) with those same first 3 digits only. So a bank transaction with an amount of $53,467.01 then returned the value "534" in my new Converted Amt field. – CluelessJeremy Mar 09 '16 at 21:26
  • The desired result is, if the TypeCode of the transaction is between 400-700, the tool will take that transaction Amt and return the reverse (*-1) in the Converted Amt field. If the TypeCode is <400, the original transaction amount will be returned in the Converted Amt field. I'm still not sure if this can even be done; the above comment from Default is correct in that I'm not trying to do any pattern matching (that I'm aware of). I'm trying to perform data manipulation using an IF statement (or Where clause for SQL). – CluelessJeremy Mar 09 '16 at 21:27
  • Open to further feedback and clarification if I'm trying to do something outside the realm of capabilities. – CluelessJeremy Mar 09 '16 at 21:27
  • From your description, you are using the regular expression to search the amount field instead of the TypeCode field and then outputing the regular expression match instead of -1*Amt. Your problem, then is deeper than simply finding the correct regular expression. If you specify which tool you are using to do this calculation, then perhaps someone with expertise in that tool can help you get further. – kaisquared Mar 09 '16 at 22:51
0

I feel your pain...no bank makes it easy. '\d' means exactly '[0-9]', '\d' is shorter. Banks have done a good job at fooling the masses that the word 'credit' is good and 'debit' is bad (me thinks that is their intent...). You can't know if the value should be negative or positive unless you KNOW which direction the money is flowing in. IF the table has separate columns for INWARDS/RECEIVED funds and OUTWARDS/PAID funds, then you do not need positive and negative indication. IF, however, there is only ONE column in the table for all AMOUNTS, whether moved INTO or taken OUT FROM the account, then you definitely need SIGNED VALUES (positive/negative indication).

Either "-$nnn" or "($nnn)", with or without the "$".

If you have TWO COLUMN tables (PAID IN and PAID OUT) then just use "$nnn" without SIGNS. If you have a SINGLE COLUMN table, then you can replace "$" with "-$" using:

$value =~ s/\$/-\$/;

The above is a perl example. '\$' means "literal SIGIL" (i.e. dollar sign).

To match any value between and including 399-700 use the following regex:

^(399|[4-6]\d\d|700)$

That should match exactly what you want.

So you could do something like (in perl):

if ($TypeCode =~ m/^([4-6]\d\d|399|700)$/) {   # if code matches pattern
    # -EITHER-
    $Amount=~ s/\$/-\$/;   # prepend "-" to "$"
    # -OR-
    $Amount=~ s/\$/-/;     # replace "$" with "-"
}

The '^' (carat - start of line or string) and '$' (sigil - end of line or string) surrounding the regex stop it from matching anything with 4 or more digits, like 3399 or 47421. I moved '[4-6]\d\d' to the front as that will match 300 of 302 possible codes (and, I dunno, it may save a few milliseconds of processing).

'[4-6]' = '[456]', which means the digits from '4' to '6'.

TEST THIS ON SAMPLE DATA FIRST!

skeetastax
  • 1,016
  • 8
  • 18