Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Split a field in MS SQL 2000

Posted on 2004-10-25
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 12405529
Have a look at http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html to create a split function in SQL
LVL 70

Accepted Solution

Scott Pletcher earned 500 total points
ID: 12405581
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 :-) .

Expert Comment

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

Author Comment

ID: 12428111
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 ?

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

596 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