I created a vb.net app that writes records to a sql server database via a text box. It writes two values(a number and a date) into one string with a space between the two values. After the values are added to the table i want to parse the values out into two different columns.The problem is that charindex cannot find the space. If i manually go into the table and create values with spaces charindex will work. I used Len to see if the blank values were legit and the select statement counted every space. I'm not sure what the problem is and this is drivining me insane!!!!!! Has anyone ever seen this issue before and found a solution? Any help is greatly appreciated.
Thank you
example data
10 2012/02/02
100 2012/02/02
1 2012/03/01
vb.net command For line = 0 To txtString.Lines.Count - 1
Using Cmd As New SqlCommand(Sql, conn)
Cmd.Parameters.Add("@Values", SqlDbType.VarChar).Value = textBoxLines(line).Trim Cmd.ExecuteNonQuery()End Using
Next
sql command (TABLE DATATYPE IS VARCHAR(50))
select
number = case when string like '% %' then substring(string, 1, charindex(' ',string)-1) end,
date = SUBSTRING(string,PATINDEX('% %',string)+1,LEN(string))
from
table
sql output
number date
----------------------------------------------------
10 2012/02/02
100 2012/02/02
1 2012/03/01