Searching with regular expressions


#1

I am a total regex newbie trying to use regex to help clean up an address file. The file (exported from Endicia) has company names mixed in with addresses in the same field.

All the addresses begin with digits, so I’d like to either select all the records that begin with letters [A-Z] or alternately all the records that begin with digits. Searching online I see the carat ^ used to match the beginning of a string in regex but that doesn’t seem to fly in Panorama.

Thanks in advance,
Barry


#2

While it might be nice to learn to use regular expressions, you do not need to for this. Just look at the first letter of the field and select those which are between “1” and “9”, assuming that no address begins with 0. That should be a search on strings, not values.


#3

Select striptonum(FieldName[1,1])≠""


#4

How do I search/select a range of numbers?

More specifically, how do I correctly write:

Select records beginning with 1-9 in field X ?


#5

To answer your regex question:

regexarray("Apple Computer, Inc. 1 Infinite Loop Cupertino, CA 95014","^[A-Z ,.]+")
       returns "Apple Computer, Inc."

and:

regexarray("Apple Computer, Inc. 1 Infinite Loop Cupertino, CA 95014","\d[A-Z ,0-9]+")
       returns "1 Infinite Loop Cupertino, CA 95014"

#6

Select X[“1-9”,1]≠""


#7

Making progress after stripping leading spaces that I didn’t realize were in the field. Sigh.

New questions:

  1. How best to strip out/delete the zip codes from records like this:

NEW YORK NY 10032-4434

I thought of striptoalpha but that also deletes the spaces.

  1. Easiest way to insert a dash to correctly format zip codes? Currently they look like this: 770694525

thanks!
b


#8

arraylast(“NEW YORK NY 10032-4434”," ") returns “10032-4434”
“NEW YORK NY 10032-4434”[1,“≠-0-9, ,-”] returns “NEW YORK NY”

“770694525”[1,-5]+"-"+“770694525”[-4,-1] returns “77069-4525”


#9

All fixed. Whew! Thanks everyone!