Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to parse out the a field so that IF there is a number, it is a separate field than the string text.
The field is called DOC_VALUE_CLEAN:
DOC_VALUE,
trim( SubField( trim(DOC_VALUE),',')) as DOC_VALUE_CLEAN,
In the examples below, those with a final numbers should have the string in a subfield called DOC_VALUE_PARSE and the number should be in a subfield called DOC_VALUE_CNT. As you can see, not all options have a final number.
try this:
if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 , SubField(DOC_VALUE, ' ' , -1 ) ) AS DOC_VALUE_NUM,
if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 ,
MID( DOC_VALUE, 1 , (len(DOC_VALUE)- len(SubField(DOC_VALUE, ' ' , -1 )))
) AS DOC_VALUE_STRING
Saludos!!!
Try something like:
Trim(SubField(String, ' ', -1))
The Subfield with a -1 will get the first field from the right side and the trim gets rid of any spaces.
You might have to put an if Len stmt around that to filter out the ones w/out numbers.
Maybe like this:
if (IsNum(SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2)), SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2), '-') as [DOC_VALUE_CNT]
if (IsNum(SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 2)), SubField([DOC_VALUE_CLEAN], 'for the following number of days ', 1) & 'for the following number of days', [DOC_VALUE_CLEAN]) as [DOC_VALUE_PARSE]
Not every value contains 'for the following number of day'
It should still work. If that phrase does not exist, then the whole text is the first subfield.
try this:
if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 , SubField(DOC_VALUE, ' ' , -1 ) ) AS DOC_VALUE_NUM,
if ( wildmatch(right(DOC_VALUE, 1 ) , '1','2','3','4','5','6','7','8','9','0') > 0 ,
MID( DOC_VALUE, 1 , (len(DOC_VALUE)- len(SubField(DOC_VALUE, ' ' , -1 )))
) AS DOC_VALUE_STRING
Saludos!!!