Issue On Values when separate it to value and text?

ahmedbarbary · June 29, 2020, 3:14am

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 :

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

harishgg1 · June 29, 2020, 8:52am

hi

how will the data be ..

will the TextUnit be always the same V ..     1.71V, 2.375V, 3.135V 

or it can be different     1.71V, 2.375T, 3.135X 

or can it have more than 1 character ..     1.71VA, 2.375XXX, 3.135SLK

ahmedbarbary · June 29, 2020, 9:24am

thank you for reply
different charachters as below format (third option you write above )

1.71VA, 2.375XXX, 3.135SLK

ahmedbarbary · June 29, 2020, 10:18am

Msg 208, Level 16, State 1, Line 60
Invalid object name 'STRING_SPLIT'.

ahmedbarbary · June 29, 2020, 10:42am

I don't have sql sever 2016 i have SQL server 2012

harishgg1 · June 29, 2020, 10:46am

use

Jeff Moden's Delimited 8K split function

ahmedbarbary · June 29, 2020, 10:47am

can you show me which function you mean

ahmedbarbary · June 29, 2020, 10:54am

I try to do that
SELECT a.Value
, left (b.value,patindex('%[A-Z]%',b.value)-1) as TextUnit
, right(b.value,patindex('%[0-9]%',b.value)+1) as ValueUnit
FROM finaltable a
cross apply
DelimitedSplit8K b
after i run script of unction above
but it give me error
Invalid column name 'value'.

harishgg1 · June 29, 2020, 10:58am

Looks like you have very little experience in SQL !!! ..

see my solution ... where i am using string_split put DelimitedSplit8K

Njoy

ahmedbarbary · June 29, 2020, 10:58am

but you use split_string and this is not available on sql server 2012

harishgg1 · June 29, 2020, 10:59am

Looks like you have very little experience in SQL !!! ..

see my solution ... where i am using string_split put DelimitedSplit8K

Njoy

ahmedbarbary · June 29, 2020, 11:02am

sorry i confuse
i try that
; with cte as
(
SELECT a.Value
, left (b.value,patindex('%[A-Z]%',b.value)-1) as TextUnit
, right(b.value,patindex('%[0-9]%',b.value)+1) as ValueUnit
FROM finaltable a
cross apply
dbo.DelimitedSplit8K(a.Value, ',') b
)
SELECT DISTINCT ST2.Value
, SUBSTRING(( SELECT ','+ST1.TextUnit AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
, SUBSTRING(( SELECT ','+ST1.ValueUnit AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2
butit give me invalid column a.value

ahmedbarbary · June 29, 2020, 11:05am

this solution is suitable to me if run without error

; with cte as
(
SELECT a.Value
, left (b.value,patindex('%[A-Z]%',b.value)-1) as TextUnit
, right(b.value,patindex('%[0-9]%',b.value)+1) as ValueUnit
FROM finaltable a
cross apply
dbo.DelimitedSplit8K(a.Value, ',') b
)
SELECT DISTINCT ST2.Value
, SUBSTRING(( SELECT ','+ST1.TextUnit AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
, SUBSTRING(( SELECT ','+ST1.ValueUnit AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2

ahmedbarbary · June 29, 2020, 11:15am

still give error but on two lines as below
Invalid column name 'Value'.
SELECT a.Value
and
[dbo].DelimitedSplit8K b

ahmedbarbary · June 29, 2020, 11:42am

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')


; with cte as 
(
SELECT   a.Value       
       , left (b.item,patindex('%[A-Z]%',b.item)-1) as TextUnit
	   , right(b.item,patindex('%[0-9]%',b.item)+1) as ValueUnit
FROM finaltable a  
        cross apply 
     [dbo].[DelimitedSplit8K](a.Value, ',')  b
	 )
SELECT DISTINCT ST2.Value
               , SUBSTRING(( SELECT ','+ST1.TextUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
	           , SUBSTRING(( SELECT ','+ST1.ValueUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2

harishgg1 · June 29, 2020, 11:53am

for my solution i created a real table ... finaltable without #

in your solution .. my code needs to be changed to #finaltable

i changed it .. please see if this works

; with cte as 
(
SELECT   a.Value       
       , left (b.item,patindex('%[A-Z]%',b.item)-1) as TextUnit
	   , right(b.item,patindex('%[0-9]%',b.item)+1) as ValueUnit
FROM #finaltable a  
        cross apply 
     [dbo].[DelimitedSplit8K](a.Value, ',')  b
	 )
SELECT DISTINCT ST2.Value
               , SUBSTRING(( SELECT ','+ST1.TextUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
	           , SUBSTRING(( SELECT ','+ST1.ValueUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2

ahmedbarbary · June 29, 2020, 11:56am

it worked but for single value not work

 Value                                   ValueUnit                      TextUnit                              

1.71Vz, 2.375Vg, 3.135Vz    1.71, 2.375, 3.135        Vz,5Vg,5Vz 
1.89Vz, 2.625V, 3.465Vz      1.89, 2.625, 3.465       Vz,25V,5Vz 
1.8V                                           1.8                                8V

harishgg1 · June 29, 2020, 12:10pm

hi

i fixed it here !!

; with cte as 
(
SELECT    a.Value 
        , left (b.item,patindex('%[A-Z]%',b.item)-1) as TextUnit
	    , right(b.item,len(b.item)-(patindex('%[A-Z]%',b.item)-1)) as ValueUnit	   
FROM #finaltable a  
        cross apply 
     [DelimitedSplit8K](a.Value, ',')  b
	 )
SELECT DISTINCT ST2.Value
               , SUBSTRING(( SELECT ','+ST1.TextUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('') ), 2, 1000) TextUnit
	           , SUBSTRING(( SELECT ','+ST1.ValueUnit  AS [text()] FROM cte ST1 WHERE ST1.Value = ST2.Value ORDER BY ST1.Value FOR XML PATH ('')), 2, 1000) ValueUnit
FROM cte ST2

ahmedbarbary · June 29, 2020, 12:26pm

thank you very much it solved