CR 2016
SQL Server
I am extracting phone numbers from a text field. The phone numbers are in varying formats with spaces, dashes, slashes, etc.
ExtractString function works most of the time but sometimes there will not be a specific character following the phone number, so there would be a space before the next character.
99% of time it will look like this in the text field with a consistant use of slashes:
LOCATION:123 MAIN ST \PHONE:213/123-8902 \COMP:AT&T
I can easily handle this with the ExtractString formula
But what I am trying to solve, is when there is not an ending "\" after the phone number
\PHONE:213/123-8902
\PHONE:213/123-8902 ADDITIONAL TEXT
Can you use a space " " in an ExtractsString formula? I can't seem to get that to work
Here's my formula which works for most instances
if {Table.info} like "*\PHONE:*" then
ExtractString({Table.info}, "\PHONE:", "\")
else
if {Table.info} like "*\PH:*" then
ExtractString({Table.info} "\PH:", "\")
else
if {Table.Info} like "*\PNUM:*" then
ExtractString({Table.info}, "\PNUM:", "\")
Need to come up with something that catches some anomolies and when there is no "\" after the phone number.
If there are no more characters after the number then I can use the right function, but this doesn't hold true for everthing.
SQL Server
I am extracting phone numbers from a text field. The phone numbers are in varying formats with spaces, dashes, slashes, etc.
ExtractString function works most of the time but sometimes there will not be a specific character following the phone number, so there would be a space before the next character.
99% of time it will look like this in the text field with a consistant use of slashes:
LOCATION:123 MAIN ST \PHONE:213/123-8902 \COMP:AT&T
I can easily handle this with the ExtractString formula
But what I am trying to solve, is when there is not an ending "\" after the phone number
\PHONE:213/123-8902
\PHONE:213/123-8902 ADDITIONAL TEXT
Can you use a space " " in an ExtractsString formula? I can't seem to get that to work
Here's my formula which works for most instances
if {Table.info} like "*\PHONE:*" then
ExtractString({Table.info}, "\PHONE:", "\")
else
if {Table.info} like "*\PH:*" then
ExtractString({Table.info} "\PH:", "\")
else
if {Table.Info} like "*\PNUM:*" then
ExtractString({Table.info}, "\PNUM:", "\")
Need to come up with something that catches some anomolies and when there is no "\" after the phone number.
If there are no more characters after the number then I can use the right function, but this doesn't hold true for everthing.