Performing mathematical calculations with numbers extracted from strings using STRTOK

Database
Fan

Performing mathematical calculations with numbers extracted from strings using STRTOK

I apologize if this is a duplicate post: I tried to edit the original and now it seems to have disappeared (first-time poster).

 

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.

 

Phil

Tags (1)

Accepted Solutions
Senior Apprentice

Re: Performing mathematical calculations with numbers extracted from strings using STRTOK

Hi Phil,

 

You've got a working answer (which is the important thing) but to comment on some of your points:

1) when I extract numbers from a VARCHAR string using STRTOK they are automatically converted to numeric values,

DW: The output from STRTOK is a VARCHAR as per the TD docs on STRTOK. This can be verified by using the TYPE function in SQL as in TYPE(strttok(...))

2) STRTOK appears to ignore spaces, at least when extracting numbers, and

DW: The output from STRTOK is everything between the previous delimiter and the following delimiter (for the token that you want). This can be verified using the CHARS function. In your example data shown above, the 'bill_width' is returned as a 5 character string, namely ' 20.0' <<< note a leading space.

3) you can't perform mathematical calculations using aliases derived using STRTOK.

DW: I can.  Smiley Happy

 

I ran your code on my system and did not have any problems.

create set volatile table vt1
 (col1 integer
 ,pkg_dim_txt varchar(100)
 )
 unique primary index(col1)
 on commit preserve rows;
 
insert into vt1 values(1,'21.0x 20.0x  8.0');
insert into vt1 values(2,'21.1x 20.4x  8.7');
 
select col1
    ,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
     ,bill_length * bill_width * bill_height / 166 AS adj_dim_weight
from vt1 as bci
order by col1;

My output from that is:

phil_image.png

 

I was using TD 15.10.1.1. What release are you trying this on?  You may have hit a bug.

(I don't think this is your problem,but) What tool were you using to run these SQL statements? I ran the above from Studio 16.10.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Performing mathematical calculations with numbers extracted from strings using STRTOK

Hi Phil,

 

You've got a working answer (which is the important thing) but to comment on some of your points:

1) when I extract numbers from a VARCHAR string using STRTOK they are automatically converted to numeric values,

DW: The output from STRTOK is a VARCHAR as per the TD docs on STRTOK. This can be verified by using the TYPE function in SQL as in TYPE(strttok(...))

2) STRTOK appears to ignore spaces, at least when extracting numbers, and

DW: The output from STRTOK is everything between the previous delimiter and the following delimiter (for the token that you want). This can be verified using the CHARS function. In your example data shown above, the 'bill_width' is returned as a 5 character string, namely ' 20.0' <<< note a leading space.

3) you can't perform mathematical calculations using aliases derived using STRTOK.

DW: I can.  Smiley Happy

 

I ran your code on my system and did not have any problems.

create set volatile table vt1
 (col1 integer
 ,pkg_dim_txt varchar(100)
 )
 unique primary index(col1)
 on commit preserve rows;
 
insert into vt1 values(1,'21.0x 20.0x  8.0');
insert into vt1 values(2,'21.1x 20.4x  8.7');
 
select col1
    ,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
     ,bill_length * bill_width * bill_height / 166 AS adj_dim_weight
from vt1 as bci
order by col1;

My output from that is:

phil_image.png

 

I was using TD 15.10.1.1. What release are you trying this on?  You may have hit a bug.

(I don't think this is your problem,but) What tool were you using to run these SQL statements? I ran the above from Studio 16.10.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Performing mathematical calculations with numbers extracted from strings using STRTOK

Thanks for your reply, Dave.

 

So, if I cast these columns using STRTOK as decimals, that does appear to eliminate the spaces, is that right? I can't find anything definitive about whether spaces are emilinated when casting as decimal. I can't have the string that STRTOK searches for be 'x ' (<<note the space) because there could be anywhere between 0 and 2 spaces after the x. If casting as decimal doesn't eliminate the spaces, I could just use trim, but I don't want to add that in needlessly.

 

I tried running the SQL you provided and that runs the calculation for me as well. But when running it in my actual code, it doesn't work. I've checked that the aliases are named uniquely. I'm using TD SQL Assistant. When I look at "about" I see three different versions, and I don't know which to share with you, so I'll share them all:

Version 15.00.0.04

Database Version 15.10.04.06

Privider Version 15.0.0.0

 

Highlighted
Senior Apprentice

Re: Performing mathematical calculations with numbers extracted from strings using STRTOK

Hi Phil,

 

In this instance it is probably the 'database version' that is relevant (if indeed the processing is version dependent). So you're running:  15.10.04.06

(FYI If you ever want to check what the database version is then run "SELECT * FROM DBC.DBCInfoV;" - providing that you're not using Unity Director in which case you may not get the 'real' answer).

- I've just run my code on 15.10.5.8 and it still works.

 

When Teradata converts a string to a numeric data type leading and trailing spaces are removed. See "SQL Functions, Operators, Expressions, and Predicates" (page 527 for TD15.10) where it says "Leading pad characters are ignored. Trailing pad characters are ignored, except for signed zoned decimal input.".

- So you don't need the TRIM function.

 

So in your original data of "21.0x 20.0x  8.0", the STRTOK output is

'21.0'

' 20.0'

' 8.0'

These are all VARCHARs and the last two have leading spaces (pad characters).

During the implicit character to numeric conversion, any leading or trailing spaces are removed, leaving

'21.0'

'20.0'

'8.0'

Which should (!) be treated as simple decimal values.

 

A couple of possibilities:

- is the radix separator for your session set to something other than '.'? Use a HELP SESSION command to check this.

- is it possible for you to post your actual code?

- for the code that 'doesn't work' do you have Query Logging turned on? This will show us what sql the dbms is actually receiving. There have been situations where the api level driver (particularly odbc) changes the sql. Rare, but it happens.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Performing mathematical calculations with numbers extracted from strings using STRTOK

The radix separator for your session set to '.'. Unfortunately, I've just been pulled onto an urgent project, so I will have to be satisfied with my "working solution" for now. Thank you so much for your help, Dave.