 # SQL Server

Hi, can someone help please? I have a list like the one below that got multiple cost for different activities within a row and would like to get the sum of the total cost of those multiple activities using SQL Server.
create table #Temp (id int, Rate nvarchar(500), Value_Expected int)
insert into #Temp values
('1', '1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93',14.11),
('2', '1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 ',5.67),
('3', '1 x E1,"z, z, z gas/water/z (if required), z (z)",4.67 1 x E6b,z (z)11 - 20m,2 1 x E6c,z (z) 21 - 30m,3 1 x E7b,xyz (z)11 - 20m,2 1 x E8,z,0.5 2 x E10,z,1 1 x E11,z,0.75',6.34)

select * from #Temp

drop table #Temp

Looking at row 1 for instance the expected total result should be 14.11 after addition and multiplication.

"1 x E1 (L) - 5.77
6 x E10(L) - 1.235
1 x E11(L) - 0.93"

what is E1 (L), E10 (L) and E11 (L)??

'1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93'

'1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 '

What does

• As above (E10) but xyz (vvv rate)
• Day rate per hour
• (if required)
• (inflated rate)
• ...

mean???

What are we supposed to understand by all of this?

Why do you want to solve this by SQL?
The data is not structured. It is not meant to be processed by SQL.

this might give you a start :-

``````;with cte as (
SELECT
Rate,

CASE
WHEN delim_1 > 0 THEN LEFT(Rate, delim_1 +3)
ELSE Rate
END AS first_column,
CASE
WHEN delim_2 > 0 THEN SUBSTRING(Rate, delim_1 + 3, delim_2 - delim_1 +3)
WHEN delim_1 > 0 THEN SUBSTRING(Rate, delim_1 + 3, 500)
ELSE ''
END AS Second_column,
CASE
WHEN delim_3 > 0 THEN SUBSTRING(Rate, delim_2 + 3, delim_3 - delim_2 + 3)
WHEN delim_2 > 0 THEN SUBSTRING(Rate, delim_2 + 3, 500)
ELSE ''
END AS Third_column
FROM #Temp
CROSS APPLY (SELECT CHARINDEX('.', Rate) AS delim_1) AS ca1
CROSS APPLY (SELECT CASE WHEN delim_1 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_1 + 3) END AS delim_2) AS ca2
CROSS APPLY (SELECT CASE WHEN delim_2 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_2 + 3) END AS delim_3) AS ca3
CROSS APPLY (SELECT CASE WHEN delim_3 = 0 THEN 0 ELSE CHARINDEX('.', Rate, delim_2 + 3) END AS delim_4) AS ca4

),cte2 as (
select

left(substring(first_column,charindex('x',first_column)-2, charindex('x',first_column)),1) Quantity_1
--,convert(float, left(first_column,1)) Quantity_1
,convert(float,substring(first_column,charindex('.',first_column)-1,4)) as Price1

,left(substring(Second_column,charindex('x',Second_column)-2, charindex('x',Second_column)),1) Quantity_2
,convert(float,substring(Second_column,charindex('.',Second_column)-1,4)) as Price2

,left(substring(Third_column,charindex('x',Third_column)-2, charindex('x',Third_column)),1) Quantity_3
,convert(float,substring(Third_column,charindex('.',Third_column)-1,4)) as Price3
,Third_column
from
cte

),cte3 as (
select

Quantity_1
,case when price1 not like '%.%' then null else price1 end as price1
,Quantity_2
,case when Price2 not like '%.%' then null else Price2 end as Price2
,Quantity_3
,case when Price3 not like '%.%' then null else Price3 end as Price3
,Quantity_1	* Price1 as  
,Quantity_2	 * Price2 as 
,Quantity_3	 * Price3 as 
from cte2
) select

*,
++ as total

from cte3``````

hi

i was able to use user defined function to split the string into small pieces

from this we have to ...find before x one and identify and multiply value ..
seems complicated ..

simple cursor .. character by character LOOP of string .. with logic ... if then else ..
will do it
i think ...

please click arrow to the left for UDF and SQL ...
``````drop function [dbo].[fn_split_string_to_column]
go

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0

SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END

WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)

INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]

SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END

RETURN
END
GO

;with cte as
(
select rate ,value
from Temp  cross apply [dbo].[fn_split_string_to_column](rate,' ')
)
select a.Rate,b.value from cte  a cross  apply [dbo].[fn_split_string_to_column](value,',')  b
``````

Wow! This's great. Let me try it. Thank you very much

Thanks a lot. Let me try it

You are right.
Data not in a properly machine-readable format. I'm only new in the company.
Thanks

please click arrow to the left for Drop CREATE Data
``````drop table Temp
go

create table Temp (id int, Rate nvarchar(500), Value_Expected int)
go

insert into Temp values
('1', '1 x E1 (L),As above (E1) but xyz (inflated rate),5.77 6 x E10(L),As above (E10) but xyz (vvv rate),1.235 1 x E11(L),As above (E11) but xyz (vvv rate),0.93',14.11),
('2', '1 x E1,"xyz, new spur, xyz/water/xyz (if required), xyz (xyz)",4.67 1 x E10,Day rate per hour,1 ',5.67),
('3', '1 x E1,"z, z, z gas/water/z (if required), z (z)",4.67 1 x E6b,z (z)11 - 20m,2 1 x E6c,z (z) 21 - 30m,3 1 x E7b,xyz (z)11 - 20m,2 1 x E8,z,0.5 2 x E10,z,1 1 x E11,z,0.75',6.34)
go

select * from Temp
go
``````

Here's the cursor .. going though character by character ...

``````declare @string varchar(500) = '' ,  @i int , @Cursor as CURSOR;
set  @Cursor = cursor for select rate from temp
open @Cursor; FETCH NEXT FROM @Cursor INTO @string

WHILE @@FETCH_STATUS = 0
BEGIN
select @i = 0
while @i < len(@string)
begin
select @i = @i + 1
select  substring(@string, @i, 1)
end
FETCH NEXT FROM @Cursor INTO @string
END

CLOSE @Cursor;
DEALLOCATE @Cursor;``````