Search using wild cards 1

[Dausa67]

Good Morning,
I am trying to query all part numbers in my database that meet certian conditions. The only problem that I have not been able to figure out is how to search for part numbers that have a dash (-) between a alph and numeric character.

Example; I need to find the following type of part numbers.
ABCD-1243
1234-XYZ

Can I use a wild card to bring all of the part numbers back to my report?

Clay

 

[lbass]

Why does the dash present a problem? How are you searching? What kinds of formulas have you been using? Is your search limited to some part of this field?

-LB

 

[Dausa67]

Thanks for your response.

As a business we have to follow ATA standards (Air Transport Association of America). If a part number has a dash between two letters or between a letter and a number, then it does not meet ATA standards. B-A-123-C-2-1 could be set up in many different ways which does happen and does cause an enormous amount of trash.

So far my search criteria is as follows:

{ITEM_ID} like ["*@*", "*$*", "*^*", "*&*", "*#*", 
"*(*", "* *", "*+*", "*'*", '*"*', "*%*", "*.*", "*/*"]

Search is limited to part number only.

Clay

 

[lbass]

So what happens when you use:

"*-*"

...in your array?

-LB

 

[Dausa67]

It brings back all parts (over 500,000 parts) that have a dash in the part number. If it has a dash between a number and a number, I do not want to see those. I just want to see part numbers that have a dash between two letters such as 1234-BRFW.



Clay

 

[Dausa67]

I need to find part numbers like
124B-4
1243-9
145B-B


I do not need to find or see part numbers like
1241-9
1459-1

Does this make sense?

Clay

 

[lbass]

Sorry, forgot to reply. Try a formula like this:

stringvar array x := split({table.string},"-");
numbervar i;
numbervar j := ubound(x);
numbervar y;
for i := 1 to j do(
if j > 1 and
not isnumeric(x) then
y := y + 1
);
y > 0

This should identify records that contain a hyphen and a non-numeric array element.

-LB

 

[Dausa67]

As always thank you for your help.

It did not work. Should I try "Add Command"?

I spoke to a SQL guy in our MIS department and he gave me the following code. The problem is that I do not really understand what this means.

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))); would return 1 

  LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))); would return 2 

  LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))); would return null 

  LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))); would return null

Clay

 

[lbass]

What do you mean by "it didn't work?" I did test this. Please explain where you placed the formula and the results that were returned.

-LB

 

[Dausa67]

I created a formula using only the code you gave me.

stringvar array x := split({table.string},"-");
numbervar i;
numbervar j := ubound(x);
numbervar y;
for i := 1 to j do(
if j > 1 and
not isnumeric(x[i]) then
y := y + 1
);
y > 0

The results were true or false for the same style part.
Example; F50B134000325B2 was False
F50B161000115B3 was True
AV200-RS-F50 was True
AV9690-01-2 was also True



Clay

 

[lbass]

I don't believe the second one would appear as True, since there are no hyphens and j would = 1. Are you sure you noted the string correctly?

For the last one, I guess you don't want that to be true, but this is the first example where you have shown letters in the string that were NOT next to any hyphen. This is important information that changes the solution. Try this instead:

numbervar x := {table.string};
numbervar i;
numbervar j := len(x);
numbervar y := 0;
for i := 1 to j do(
if x = "-" then
if not isnumeric(x[i-1]) or
not isnumeric(x[i+1]) then
y := y + 1
);
y > 0

-LB

 

[TeyBrady]

I got lost when 1243-9 was a part that was wanted and 1241-9 was not wanted.

You might want to go from 2 to j-1 in the loop.

If you are using Oracle you could try a regular expression check.

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

 

[Dausa67]

I was able to get it to work. Thanks LBass.

Clay