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

Question

I have a table in SQL that looks like the below:

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.

Please tag your question with the database that you are using. — Popeye, Jan 12 '21 at 04:09
You need to use string manipulation functions to find the exact characters representing an individual number, then cast that string to an integer. After you've cast each part to integers, then you can multiply those two values. Exactly how varies based on the SQL dialect you're using; SQL Server, Oracle, MySQL, PostgreSQL, SQLite, etc, etc. — MatBailie, Jan 12 '21 at 04:11

score 0 · Accepted Answer · answered Jan 12 '21 at 04:23

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

score 0 · Answer 2 · answered Jan 12 '21 at 13:37

Given the structure of the data, you could use simple string operations:

select t.*,
       multiple * try_convert(int, left(b4, charindex('*', b4) - 1)),
       multiple * try_convert(int, left(b5, charindex('*', b5) - 1)),
       multiple * try_convert(int, left(b6, charindex('*', b6) - 1))
from t;

Here is a db<>fiddle.

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

2 Answers2