Split a field in MS SQL 2000

Posted on 2004-10-25
Last Modified: 2008-03-17
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.
Question by:petant
    LVL 8

    Expert Comment

    Have a look at to create a split function in SQL
    LVL 68

    Accepted Solution

    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 :-) .
    LVL 9

    Expert Comment

    Hopefully you don't have any spaces other that one, other wise things could get messy.

    Author Comment

    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 ?
    LVL 9

    Expert Comment

    Use the ISNULL function to filter it out. You can look it up in Books online. It is simple to undrestand and use.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now