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

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2694

    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
  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    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 week, 4 days ago by  Mr. Brian Gale. Reason: Code update
    • This reply was modified 1 week, 4 days ago by  Mr. Brian Gale. Reason: removed the USE statement as it likley doesn't apply in your case
  • pietlinden

    SSC Guru

    Points: 62811

    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;
  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    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.

  • pietlinden

    SSC Guru

    Points: 62811

    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 5 (of 5 total)

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