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
