Locate Credit Card Number in Text Field

[HawnRyan]

I have a real problem trying to filter fields from an Informix Database with CR11. I need to locate credit card numbers in a text field in multiple variations (####-####-####-####, #################, etc.)

I've tried a lot of different methods to filter the results by locating a series of numbers grouped together in a text field but have miserably failed each time.

I do not have direct access to the Informix Database to run a SQL statement. I need to use Crystal Reports to locate records that may include a Credit Card number.

Any assistance you can provide is greatly appreciated.

 

[HawnRyan]

Ideally. I'd like to use a statement similar to:

WHERE column LIKE
'%[0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9]%'

but not sure if this is possible with Crystal Reports.

Please HELP!

 

[lbass]

You could try something like the following:

stringvar array x := split({table.field}," ");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
if isnumeric(replace(x,"-","")) then
y := x
);
y

-LB

 

[HawnRyan]

Thanks lbass!

I tried your suggestion but got an error message when trying to add it to the query; "A boolean is expected" then the first line is highlighted.

I need some real help here...

 

[lbass]

This is just a plain old formula that you create in the field explorer->formula editor. I thought you wanted to extract the number from the field.

To select fields that contain a credit card number, use a formula like this in report->selection formula->record:

stringvar array x := split({table.field}," ");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
if isnumeric(replace(x,"-","")) then
y := {table.field}
);
{table.field} = y

-LB

 

[HawnRyan]

Sorry lbass,

I'm trying to filter my selection by records that include credit card numbers or a string of 8 or more numbers included in a text field.

The table has an exorbinate amount of records and I need to quickly list the records with possible credit card numbers in them for review.

Does that make sense?

 

[lbass]

Did you try my suggestion in the last part of my last post? I tested it and it worked.

-LB

 

[TeyBrady]

I don't know much about informix but in Oracle you could use REGEXP_SUBSTR to suck the credit card number out of the field or REGEXP_INSTR to tell you what position in the field the credit card looking number starts.

You may be able to add this as an SQL expression something like this:
REGEXP_SUBSTR("table"."textfield", '[0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9]')

or I think you can shorten it like this.
REGEXP_SUBSTR("table"."textfield", '[0-9]{4}[- ][0-9]{4}[- ][0-9]{4}[- ][0-9]{4}')

For informix you may need this which returns true or false:
regexp_match("table"."textfield", '[0-9]{4}[- ][0-9]{4}[- ][0-9]{4}[- ][0-9]{4}')

http://www.ibm.com/developerworks/d...brary/techarticle/db_regexp.html#regexp_match

Good luck!

 

[HawnRyan]

Thanks lbass: I tried it which seemed to work but the table is soo large that the query fails. I have a date field which I can query...how can I incorporate a date spread into this formula?

Thanks TeyBrady: I don't have direct access to the Informix database, nor can I run direct SQL queries to the database. Thanks for your input...

 

[lbass]

at the end of the formula just add:

and {table.date} = {?daterange}

But what do you mean by "the query fails." Did you get an error message? It would be slow, but shouldn't fail.

-LB

 

[HawnRyan]

I'm guessing it has something to do with the range, as I receive this error message ("Failed to Retrieve Data from the Database") when I try to run a query that is too broad...when I minimize the scope (ie. data range) the query runs fine.



Here is the query I'm trying to run which fails:

stringvar array x := split({ghvisitcomments.comments}," ");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
if isnumeric(replace(x,"-","")) then
y := {ghvisitcomments.comments});
{ghvisitcomments.comments} = y and
{ghvisit.arrivaldate} in Date (2010, 03, 10) to Date (2010, 03, 20)

Thank you for all your assistance....

 

[lbass]

Are you saying it fails even with the date range?

-LB

 

[HawnRyan]

Yes...not sure if the syntax is correct for adding the date range...it differed from your example.

 

[lbass]

The syntax is fine. This formula works when I test it here, so I'm at a loss to explain why you are getting that error--unless the comments field is so long that the array exceeds 1000. Try setting number j := 1000--just to see if it runs then.

-LB

 

[TeyBrady]

I was suggesting that you use a Crystal SQL expression. I'm not sure what you mean by not having direct access to the database. It seems like if you are running a report you have some sort of access to the database. Through crystal if you want to do something a little complex on the database you can use SQL expressions or command Objects.

Crystal SQL Expression FAQ if this helps.

http://www.tek-tips.com/faqs.cfm?fid=1377