value separate to text and unit not working when value have comma separated mult

  • I work on SQL server 2012 I face issue : I can't split Value column to Text Unit and Value Unit in case I have on Value column multiple value separated by comma .

    as example

    Value                    ValueUnit            TextUnit
    1.71V, 2.375V, 3.135V 1.71 V, 2.375V, 3.135V have issue when separate it to text and value

    correct must be as line below:

           Value                     ValueUnit                TextUnit
    1.71V, 2.375V, 3.135V 1.71,2.375,3.135 V

    for single values without comma as

    Value TextUnit ValueUnit
    1.8v V 1.8 work perfect
    sample Data as below :

    sample Data as below :

    create table #finaltable
    (
    Value nvarchar(50),
    TextUnit nvarchar(50),
    ValueUnit nvarchar(50)
    )
    insert into #finaltable(Value)
    values
    ('1.71V, 2.375V, 3.135V'),
    ('1.89V, 2.625V, 3.465V'),
    ('1.8V')
    update ft set ValueUnit=substring(ft.Value,1,ca.Posit),TextUnit=substring (ft.Value,Posit+1,50) from #FinalTable ft
    cross apply (select PATINDEX('%[0-9.][^0-9.]%',ft.Value))ca (Posit)
    select * from #finaltable

    when you run statement above it will display issue on value have comma separated

    on record number 1 and number 2 but number 3 it work perfect

    so How to solve issue on records 1 and 2 have values with separated comma ?

    Expected Result it must be as below


    Value ValueUnit TextUnit
    1.71V, 2.375V, 3.135V 1.71,2.375,3.135 V
    1.89V, 2.625V, 3.465V 1.89,2.625,3.465 V
    1.8V 1.8 V

    wrong values as below AND I don't need Below :

    Value                   TextUnit            ValueUnit
    1.71V, 2.375V, 3.135V V, 2.375V, 3.135V 1.71 --have issue on this line
    1.89V, 2.625V, 3.465V V, 2.625V, 3.465V 1.89 --have issue on this line
  • I just wanted to confirm an assumption I had prior to working on this - the TextUnit value will never change in a "Value", correct?  Plus, is the "TextUnit" value is always a single character?

    What I mean is you would never have a VALUE string like "1.71V, 2.385A, 3.135O" for example or "1.71mV".  If so, this is  fairly easy to do with something like:

    CREATE table #finaltable
    (
    Value nvarchar(50),
    TextUnit nvarchar(50),
    ValueUnit nvarchar(50)
    )
    insert into #finaltable(Value)
    values
    ('1.71V, 2.375V, 3.135V'),
    ('1.89V, 2.625V, 3.465V'),
    ('1.8V')
    update ft set ValueUnit=REPLACE([ft].Value,RIGHT([ft].value,1),''),
    TextUnit=RIGHT([ft].value,1) from #FinalTable ft
    select * from #finaltable

    DROP TABLE #finalTable

    Using this we get rid of the CROSS APPLY too!  Now, if the TextUnit can change OR can be more than 1 character, then it gets more complicated.

    EDIT - This query will handle multi-character TextUnit values (such as mV):

    CREATE TABLE [#finaltable]
    (
    [Value] NVARCHAR(50)
    , [TextUnit] NVARCHAR(50)
    , [ValueUnit] NVARCHAR(50)
    );
    INSERT INTO [#finaltable]
    (
    [Value]
    )
    VALUES
    (
    '1.71mV, 2.375mV, 3.135mV'
    )
    , (
    '1.89V, 2.625V, 3.465V'
    )
    , (
    '1.8V'
    );
    UPDATE
    [#finaltable]
    SET
    [TextUnit] = SUBSTRING( [ft].[Value]
    , [posit] + 1
    , CASE
    WHEN [commaspot] = 0
    THEN LEN([ft].[Value])
    ELSE [commaspot] - [posit] - 1
    END
    )
    , [ValueUnit] = REPLACE( [ft].[Value]
    , SUBSTRING( [ft].[Value]
    , [posit] + 1
    , CASE
    WHEN [commaspot] = 0
    THEN LEN([ft].[Value])
    ELSE [commaspot] - [posit] - 1
    END
    )
    , ''
    )
    FROM[#finaltable] AS [ft]
    CROSS APPLY
    (
    SELECT
    PATINDEX('%[0-9.][^0-9.]%'
    , [ft].[Value]
    )
    ) AS [ca]([Posit])
    CROSS APPLY
    (
    SELECT
    PATINDEX('%,%'
    , [ft].[value]
    )
    ) AS [ca2]([commaspot]);

    SELECT
    [Value]
    , [TextUnit]
    , [ValueUnit]
    FROM[#finaltable];

    DROP TABLE [#finaltable];

    Have 2 CROSS APPLY's now, but the first one is used to get the numeric length and the second is to get the first occurance of a comma indicating that there is more than  1 value.

    I ran this through SQL Prompt SQL Format so the code is a bit easier to read (my opinion).

    • This reply was modified 1 year, 11 months ago by  Mr. Brian Gale. Reason: Code update
    • This reply was modified 1 year, 11 months ago by  Mr. Brian Gale. Reason: removed the USE statement as it likley doesn't apply in your case

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • I went a slightly different route... I used Jeff Moden's DelimitedSplit8K function to do this...  (some of the bits are just intermediary calculations)

    SELECT ft.RawValue
    , ca.ItemNumber
    , TrimmedItem = TRIM(ca.Item)
    --, TrimmedItemLength = LEN(TRIM(ca.Item))
    , Units = RIGHT(TRIM(ca.Item),1)
    , NumValue = CAST(LEFT(TRIM(ca.Item),LEN(TRIM(ca.Item))-1) AS DECIMAL(4,3))
    FROM #FinalTable ft
    CROSS APPLY TestDb.dbo.DelimitedSplit8k(ft.RawValue,',') ca;

    If you were using SQL Server 2016 or later, you could use this:

    SELECT NumericPart = CAST(LEFT(TrimmedValue,LEN(TrimmedValue)-1) AS DECIMAL(3,2))
    , TextPart = RIGHT(TrimmedValue,1)
    FROM (
    SELECT RawValue
    , TrimmedValue = TRIM(value)
    FROM #finaltable
    CROSS APPLY string_split(RawValue,',')
    ) ca;
  • pietlinden wrote:

    I went a slightly different route... I used Jeff Moden's DelimitedSplit8K function to do this...  (some of the bits are just intermediary calculations)

    SELECT ft.RawValue
    , ca.ItemNumber
    , TrimmedItem = TRIM(ca.Item)
    --, TrimmedItemLength = LEN(TRIM(ca.Item))
    , Units = RIGHT(TRIM(ca.Item),1)
    , NumValue = CAST(LEFT(TRIM(ca.Item),LEN(TRIM(ca.Item))-1) AS DECIMAL(4,3))
    FROM #FinalTable ft
    CROSS APPLY TestDb.dbo.DelimitedSplit8k(ft.RawValue,',') ca;

    If you were using SQL Server 2016 or later, you could use this:

    SELECT NumericPart = CAST(LEFT(TrimmedValue,LEN(TrimmedValue)-1) AS DECIMAL(3,2))
    , TextPart = RIGHT(TrimmedValue,1)
    FROM (
    SELECT RawValue
    , TrimmedValue = TRIM(value)
    FROM #finaltable
    CROSS APPLY string_split(RawValue,',')
    ) ca;

    Only thing I see wrong with  this approach is you are getting stuck with a single-character unit type.  so if the units were mV for example, you would get "V" as your  Units.  And 1000 mV vs 1000 V is a DRASTIC difference.  I know it wasn't in the original request, but it is worth looking into.

    I don't have  the DelimitedSplit8k on my system, but won't this give you one row per value in the comma separated list?  The OP wanted the result to still be a comma separated list in the end.  Just one of the columns has the UNIT only and the other has the values without units.

    I don't think this is going to give the OP what they are looking for unless I am way out to lunch on how your query works.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Brian,

    Oh right. I think I was reacting to the design flaw... =(

    Wonder if I can repivot once the two groups are separated.

    Pieter

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply