I am using the STRTOK function to extract numerical strings from a VARCHAR field, pkg_dim_txt. Here is an example of the field:
"21.0x 20.0x 8.0"
I'm extracting the numbers as follows:
,STRTOK(bci.pkg_dim_txt,'x',1) AS bill_length ,STRTOK(bci.pkg_dim_txt,'x',2) AS bill_width ,STRTOK(bci.pkg_dim_txt,'x',3) AS bill_height
This gives the results I expected:
bill_length bill_width bill_height
21.0 20.0 8.0
However, I can't perform my needed calculations with the above numbers -- I just get nulls when I try the following:
,bill_length * bill_width * bill_height / 166 AS adj_dim_weight
I thought this was because pkg_dim_txt is a VARCHAR field, with the resulting data being VARCHAR as well. So, I tried casting them as decimals:
,CAST(STRTOK(bci.pkg_dim_txt,'x',1) AS DECIMAL(4,1)) AS bill_length ,CAST(STRTOK(bci.pkg_dim_txt,'x',2) AS DECIMAL(4,1)) AS bill_width ,CAST(STRTOK(bci.pkg_dim_txt,'x',3) AS DECIMAL(4,1)) AS bill_height
But I still can't perform the above calculation. So, I tried using the TRIM function to cut off any spaces that might still exist at the beginning or end of my numbers:
,CAST(TRIM(STRTOK(bci.pkg_dim_txt,'x',1)) AS DECIMAL(4,1)) AS bill_length ,CAST(TRIM(STRTOK(bci.pkg_dim_txt,'x',2)) AS DECIMAL(4,1)) AS bill_width ,CAST(TRIM(STRTOK(bci.pkg_dim_txt,'x',3)) AS DECIMAL(4,1)) AS bill_height
Still no luck. When I try using SUBSTR and cast as decimal, I can perform calculations, so I believe this is an issue with STRTOK not having the ability to be converted to numeric values. Is there a way to convert the results from STRTOK to decimals so they can be used in mathematical calculations?
Since writing everything above, I found that if I use the function explicitly in the calculation, rather than using the aliases created previously, the calculation works:
,STRTOK(bci.pkg_dim_txt,'x',1) * STRTOK(bci.pkg_dim_txt,'x',2) * STRTOK(bci.pkg_dim_txt,'x',3) / 166 AS adj_dim_weight
As you can see, it works even without the CAST or TRIM functions. So, from all of this playing around with the STRTOK function, I got the following take aways:
1) when I extract numbers from a VARCHAR string using STRTOK they are automatically converted to numeric values,
2) STRTOK appears to ignore spaces, at least when extracting numbers, and
3) you can't perform mathematical calculations using aliases derived using STRTOK.
Is the inability to use the aliases in mathematical calculations just a glitch with the STRTOK function? Maybe there are other explanations for my experience. Can anyone shed light on this function?
I decided not to rewrite this whole post after figuring out how to make the calculation work, because this is a newer function, so I thought it might be helpful to others trying to learn how to use it.
Thanks in advance for any help.
May be there are NULL's hidden amongst Bill's.
Can you check if the bci.pkg_dim_txt has all NOT NULL VALUES?
Other possibility, one of the STROK function is returning a NULL.
You will have to check your data set.