SQLTeam.com | Weblogs | Forums

SQL Server

#1

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"
Thanks in advance

#2

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

#3

'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.

#5

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  [1]
				,Quantity_2	 * Price2 as [2]
				,Quantity_3	 * Price3 as [3]
				from cte2
) select

*,
[1]+[2]+[3] as total


from cte3
#7

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

image
image408×750 7.37 KB

#8

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

#9

Thanks a lot. Let me try it

#10

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

#11
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;