Split a field in MS SQL 2000

I have a varchar (40) field in a table that I would like to split.  The delimiter is a space.

 It currently looks like this --- >  datax datay

I want the end result to be a select statement that splits the field into two fields.  What is the best way to do this?  Note: what is in datax and datay is not constant meaning it could have different character lengths.  I don't see in sql the equivilent of split in visual basic.

Any help would be appreciated.
petantAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can use CHARINDEX() function to find a space in the column value.

--everything up to first space
SELECT LEFT(yourColumn, CHARINDEX(' ', yourColumn) - 1),  
--everything after the first space
    SUBSTRING(yourColumn, CHARINDEX(' ', yourColumn) + 1, LEN(yourColumn))
FROM ...


NOTE: If it's possible a space will not be present, this code needs changed.  Just let me know :-) .
0
 
SashPCommented:
Have a look at http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html to create a split function in SQL
0
 
apirniaCommented:
Hopefully you don't have any spaces other that one, other wise things could get messy.
0
 
petantAuthor Commented:
Well I did have some records where that field is null.    I got the following error:
Invalid length parameter passed to the substring function.
What is the best way to only check for that field that is not null as part of the select statement ?
0
 
apirniaCommented:
Use the ISNULL function to filter it out. You can look it up in Books online. It is simple to undrestand and use.
0
All Courses

From novice to tech pro — start learning today.