Read char,double,int pattern from string in sql
<p>You have a string like</p>

<pre><code>set @string = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
</code></pre>

<p>I would like to know if there is a way to extract the string values and place them in the first row,the double values and place them in the second row and the int values and place them in the third row.The string logic is like this </p>

<pre><code>"string,double,int,string,double,int..."
</code></pre>

<p>but there are cases when there is</p>

<pre><code>"string,double,int,string,double,string,double,int"
</code></pre>

<p>and I would like in the third row where the int should be to be 1 by default so the table would look something like this.</p>

<pre><code>First Row   Second Row  Third Row
ddd           1.5         1
eee           2.3         0
fff           1.2         1
ggg           6.123       1
</code></pre>

<p>I have a code where u can extract all the values from the string and place them in a row but that is not enough.</p>

<pre><code>declare @string as nvarchar(MAX)

set @string = 'aaa,bbb,ccc,ddd,1.5,1,eee,2.3,1,fff,1.2,ggg,6.123,1'
;with tmp(DataItem, Data) 
as (
select LEFT(@string, CHARINDEX(',',@string+',')-1),
    STUFF(@string, 1, CHARINDEX(',',@string+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data &gt; '')
select DataItem from tmp
option (maxrecursion 0)
</code></pre>