Parse a fixed length numeric value from a string

Total article views: 116 | Views in the last 30 days: 8

 Rate this |   Join the discussion |  Briefcase

Specifically, I needed to parse a zip code (5-digit) from a client comments fields where the number was in different positions such as "Customer 999999999 should be at zip code 99999" and "99999 zipcode" and "should be zip code 99999 phone 9999999999".

My stored procedure first filters on a [Comments] field string that contains variations of the phrase/word "zip code" (ie., "WHERER [Comments] Like '%zip code%'" OR "[Comments] Like '%zipcode%'"). I then needed a function to parse the string just for the 5-digit number. Of course this will break out and return only the first 5-digit number.

I also wanted to keep the function multi-purpose so I used a "intDigits" parm so it could be used for other fixed-length numbers. This could easily be omitted, but then would also need to return multiple numbers
(ie., "Customer 999999999 should be at zip code 99999" would need to return arg1 = 999999999, and arg2 = 99999) ...
or to call another function based on another function parsing for the location of the first numeric char in the string Substring({str},{lastcharpos} + Len(arg1)) = {nextcharpos} etc....

...which is another script altogether.

Total article views: 116 | Views in the last 30 days: 8