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
June 29, 2020 at 3:49 pm
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).
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.
June 29, 2020 at 7:46 pm
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;
June 29, 2020 at 7:54 pm
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