Multiplications contained in texts

Question

How to make the multiplications contained in a field varchar? (to get the area in case)

+------------------------------------------------------+
|                     DESCRIÇÃO                        |
+------------------------------------------------------+
|Chapa Térmica 605x745 Speed Chemfree                  |
|Chapa Térmica 665 X 910 Ryobi Fuji                    |
|Chapa Convencional P 4001 605x745 Speed IBF           |
|Chapa Térmica 665 X 910 X 030 sem Processo Ryobii IBF |
|Chapa Térmica 800x1030 Komori Chemfree                |
+------------------------------------------------------+

Looking for an alternative solution, I found a field with the type of a code for the plates, which contains the dimensions of the plates:

+--------------+------------------------------------------------------+
|     CODE     |                    DESCRIPTION                       |
+--------------+------------------------------------------------------+
| T605745SPCHE | Chapa Térmica 605x745 Speed Chemfree                 |
| T665910RYJUF | Chapa Térmica 665 X 910 Ryobi Fuji                   |
| C605745SPIBF | Chapa Convencional P 4001 605x745 Speed IBF          |
| T665910RYIBF | Chapa Térmica 665 X 910 X 030 sem Processo Ryobii IBF|
| T8001030KBF  | Chapa Térmica 800x1030 Komori Chemfree               |
+--------------+------------------------------------------------------+

I think my only problem right now is how to handle codes with 6 or 7 numbers being multiplied. Any suggestions?

Answer 1

I don’t know what language you’re working on so I’ll try to be generic in the answer:

Start by separating the lines, and in the home line use a regular expression to recognize the format, for example:

\b[0-9]\+x[0-9]\+

This is a regular expression of type sed, you can read more in

https://www.gnu.org/software/sed/manual/html_node/Regular-Expressions.html

The expression I wrote says the following: obtains an expression started by a blank space ( b) followed by one or more digits ([0-9]+), followed by an x, and then by one or more digits.

You can then separate them with a split in the symbol x, that is, separate them into two elements, the previous one and the later one to x, then convert them to numbers and simply have the area calculated.

Answer 2

With this new column, things got a lot easier. I did the following:

'Area' = (CONVERT(decimal,SUBSTRING(table.CodItem,2,3))*

        CASE WHEN SUBSTRING(table.CodItem,8,1) LIKE '[0-9]' THEN
            CONVERT(decimal,SUBSTRING(table.CodItem,5,4)) ELSE
            CONVERT(decimal,SUBSTRING(table.CodItem,5,3)) END)/1000000

The function CONVERT and the division by /1000000 I used only for conversion from mm² to m². Thank you!