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
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
Ronika JencyPosted Jun 29, 2020, 9:10 AM
ahmed elbarbaryPosted Jun 29, 2020, 1:08 AM
Ronika JencyPosted Jun 28, 2020, 10:58 PM