Unpack and Transform Unidata Values

cgrigg390 

I can only find a way with SQL batch or user define function, cannot resolve it with single SQL. This is the method:

1. Find delimeter ý repeatly to split lines, without it means reach the end of content;

2. Find ; in line as field delimeter, part befor it is the part number which you need. If a line has not  ; ，means whole line is part number. If no character before delimeter -- like ";FILTER AS-FUEL", means this dose be a line, but has not a part number.

3. Repeat step 2 in lines, pickup part numbers and concat them with delimeter ý to result.

4. Build a user define function with input nvarchar parameter and returns a nvarchar.

5. Update column V120 by the udf returns, with V8 as param.

Here is the code of udf

CREATE FUNCTION ufn_makeV120(@source NVARCHAR(MAX))--replace MAX to your v8 column size
RETURNS NVARCHAR(MAX)--replace MAX to your v120 column size
AS
BEGIN
  DECLARE @line INT
  DECLARE @col INT
  DECLARE @pos INT
  DECLARE @len INT
  DECLARE @d NVARCHAR(MAX)--replace max to your v120 column size
  SET @line = 1
  SET @col = 1
  SET @pos = 1
  SET @len = LEN(@source)
  SET @d = NULL

  WHILE ( ( @pos <= @len ) AND ( @len > 0 ) )
  BEGIN
    SET @line = CHARINDEX( N'ý', @source, @pos )
    SET @col = CHARINDEX( N';', @source, @pos )

    IF (( @line <= 0 ) OR (@line < @col )) -- last line or no desc
    BEGIN
      IF ( @line <= 0 ) SET @line = @len + 1
      IF ( @col <= 0 ) OR ( @line < @col ) --no desc
        SET @col = @line --copy to line end
    END
    ELSE
    BEGIN
      IF ( @col <= 0 ) --no desc
        SET @col = @line--copy to line end
    END

    IF (@d IS NULL) 
      SET @d = SUBSTRING( @source, @pos, @col - @pos )
    ELSE
      SET @d += CONCAT(N'ý', SUBSTRING( @source, @pos, @col - @pos ))

    SET @pos = @line + 1
  END
  RETURN @d
END

You can modify length of the parameter and returns to your need.

Then update table with SQL like this

UPDATE test SET V120 = dbo.ufn_makeV120(V8)