VB.NET - Charindex is not finding blank spaces

Asked By Cracked Egg Head on 19-Feb-13 04:14 PM

 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