Clever Geek Handbook

Split SQL character field to split columns

We have a database with data from our ERP, which is not that flexible, and we use this data to create reports in SSRS. I now have a price column for our articles / products which are very poorly formatted by ERP designers. The data is stored using "type" -identifier, the ones that cause problems are:

1 and 5: scaled ==> 2 lines/rows separated by ASCII char(9) and ASCII char(13) followed by char(10) for the second line.

As an example, this is one field:

11  999999999
16,9    11,154
This is another example:
99  1049    999999999
2   1,32    0,8

Line 1 is always QP, line 2 is always SP. I edited this to be clearer because the previous view was causing confusion. The number of char (9) -columns is variable, but the maximum is 3, so some may be 2. I am having problems splitting the second and sometimes the third column and splitting the second row into columns. The new table should be formatted as follows.

ID  identifier  QP1 SP1 QP2 SP2 QP3 SP3

The fields which are used are 'id', 'SPtype' ==>identifier, 'vk1'==>contains the prizes; and the table is 'DW_D_PRODUCT'.

Can anyone provide me with an efficient T-SQL. To deal with this problem. A stored procedure might be preferable, so it must run every night to update the prizes in the tables.

+3


source to share


1 answer


While it is not clear what the expected result is, I want to share with you the function I use when I have to deal with records as fields:

CREATE FUNCTION [dbo].[SplitString] 
(   
  @Values nvarchar(max),
  @ValueSeparator nvarchar(5)
)
RETURNS @Result TABLE
(
  Ord int,
  Value nvarchar(100) -- can be adjusted
) 
AS
BEGIN
  ;with ValuesToXML as
  (
    select CAST('<i>' + REPLACE(@Values, @ValueSeparator, '</i><i>') + '</i>' AS XML) as 'Values'
  ),
  ValuesToList as 
  (
    select  x.i.value('for $i in . return count(../*[. << $i]) + 1', 'int') as 'Ord',
            x.i.value('.', 'NVARCHAR(100)') AS 'Value' -- can be adjusted
    from    ValuesToXML
            CROSS APPLY 
            [Values].nodes('//i') x(i)
  )
  INSERT INTO @Result (Ord,Value) select * from ValuesToList;

  return;
END

Despite the fact that it is compact, the body is not directly related to understanding. It converts a nvarchar

to a table with XML

.
What's really important about this scored table function

is ... the fact that it can be used as a table in operations SELECT

, of course.

Here are some usage examples.
1) Simple call:

select * from dbo.SplitString('value1;value2;value3;value4', ';')

Result:

Ord   Value
  1   value1
  2   value2
  3   value3
  4   value4


2) According to your data:



;with records as
(
    select * from
    (
        values  (1, '11  999999999 ==> QP' + char(9) + char(13) + '16,9    11,154 ==>SP'),
                (2, '99  1049    999999999 ==>QP' + char(9) + char(13) + '2   1,32    0,8 ==>SP')
    ) as v(id, txt)
),
subrecords1 as
(
    select   r.id
            ,s.ord as ord1
            ,s.value as value
    from    records r
            cross apply
            dbo.SplitString(r.txt, char(9) + char(13)) s
),
subrecords2 as
(
    select   r.id
            ,r.ord1
            ,s.ord as ord2
            ,s.value as value
    from    subrecords1 r
            cross apply
            dbo.SplitString(r.value, '==>') s
),
subrecords3 as
(
    select   r.id
            ,r.ord1
            ,r.ord2
            ,s.ord as ord3
            ,s.ord3c as ord3c   
            ,s.value as value
    from    subrecords2 r
            cross apply
            ( 
                select   ord 
                        ,row_number() over (order by ord) as ord3c -- consecutive ord3
                        ,ltrim(rtrim(value)) as value
                from    dbo.SplitString(r.value, ' ')
                where   value != '' --this will cause ord3 not to be consecutive
            )s
)
select * from subrecords3

Result:

id  ord1  ord2  ord3  ord3c  value
 1     1     1     1      1  11
 1     1     1     3      2  999999999
 1     1     2     2      1  QP
 1     2     1     1      1  16,9
 1     2     1     5      2  11,154
 1     2     2     1      1  SP
 2     1     1     1      1  99
 2     1     1     3      2  1049
 2     1     1     7      3  999999999
 2     1     2     1      1  QP
 2     2     1     1      1  2
 2     2     1     4      2  1,32
 2     2     1     8      3  0,8
 2     2     2     1      1  SP

Does this mean anything to you?

You can break down the query above and see what each CTE returns.

Here are some tips:

  • id

    is the id of the original post
  • records with ord1 = 1

    and ord2 = 2

    always'QP'

  • records with ord1 = ord2 = 1

    are values ​​forQP

  • records with ord1 = ord2 = 2

    always'SP'

  • records with ord1 = 2

    and ord2 = 1

    are values ​​forSP

Since subrecords3

we have some filtering in the CTE, ord3 will not have consecutive values. For this reason, we are adding a column ord3c

.

subrecords3

Can now be combined with itself on the canvas id, ord1, ord2, ord3c

to display SP values ​​with QP values.

Good luck!

0


source






More articles:


All Articles