SQLTeam.com | Weblogs | Forums

Issue On Values when separate it to value and text?

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

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

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

1.71VA, 2.375XXX, 3.135SLK

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

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

use

Jeff Moden's Delimited 8K split function

can you show me which function you mean

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

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

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

Njoy :+1:

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

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

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

Njoy :+1:

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

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

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

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

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

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

hi

i fixed it here !! :crossed_fingers: :crossed_fingers:

; 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

image

thank you very much it solved