Search code examples
sqlmultiplication

SQL (MS SQL Server) return column value as the result of the multiplication

I have a table in SQL that looks like the below: enter image description here

table is like the below:

Partno   b5      b6      b7
A        3*38    4*38    5*38 
B        4*1100  8*1100  15*1100

Column b5,b6,b7 is currently nvarchar. Can I check how to convert this column to int with the value of the multiplication? E.g if column b5=3*38 I would like to return as 114 etc.

Thank you very much.

Solution

  • With the following assumption, you can use split the query and do multiplication.

    • You are using sql server
    • There is only multiplication in the columns
    • There is only two values in multiplication
    • So ultimately, you have just a*b in the columns

    You can use following query:

    Select partno, 
       PARSENAME(REPLACE(b5,'*','.'),2) * PARSENAME(REPLACE(b5,'*','.'),1) as b5_res,
       PARSENAME(REPLACE(b6,'*','.'),2) * PARSENAME(REPLACE(b6,'*','.'),1) as b6_res,
       PARSENAME(REPLACE(b7,'*','.'),2) * PARSENAME(REPLACE(b7,'*','.'),1) as b7_res
From your_table t