Solved

Divide String based on character.  In this case a -

Posted on 2003-10-22
14
352 Views
Last Modified: 2012-05-04
Using MS SQL 2000.  I need to split a string into two parts based on the -.   The parts will be divided by a '-'.
Example:    IBM-001    would end up as     'IBM'  and  '001'
               MICROSOFT-011  would end up as  'MICROSOFT' and  '011'

Thank You.
0
Comment
Question by:mikeinoxford1
  • 3
  • 3
  • 3
  • +1
14 Comments
 
LVL 9

Accepted Solution

by:
PaulBarbin earned 34 total points
ID: 9602760
If you can guarantee that the '-' will always appear, then substring and charindex will probably get you there.  Take a look at this:

DECLARE @input varchar(20)
SET @input = 'ibm-000'

SELECT      substring(@input, 1, charindex('-', @input)-1),
      substring(@input, charindex('-', @input)+1,len(@input))

Paul
0
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9602772
If not, you could check the value before parsing (say with a CASE statement or IF depending on whether you're inside a select or not).

Or you could use the ISNULL statement before the charindex to verify that the parameter passed to the substring function is NOT NULL.

Paul
0
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 33 total points
ID: 9602806
Argh.  Paul beat me to it.  At least my answer is more complete... :P

SELECT
      CASE
            WHEN CHARINDEX('-', SomeField) <> 0 THEN
                  SUBSTRING(SomeField, 1, CHARINDEX('-', SomeField)-1)
            ELSE
                  NULL
      END As FirstPart,
      CASE
            WHEN CHARINDEX('-', SomeField) <> 0 THEN
                  SUBSTRING(SomeField, CHARINDEX('-', SomeField)+1, LEN(SomeField))
            ELSE
                  NULL
      END As NextPart
FROM
      SomeTable

Enjoy,
Dex*
0
 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 33 total points
ID: 9604077
Sugestion:
if you are in SQL 2000 than create a function, an UDF that returns a certain substring based on a certain character...... this may help you in future too...

EG:
--has 3 parameters, the string, the character, the position
create function getString(@str varchar(250), @c char, @pos int)
returns varchar(100)
AS BEGIN
  declare @t int
    @tmp varchar(250)

  set @t = 1
  select @tmp = @str

  while (@pos > 1) and (@t <> 0) BEGIN
    set @t = charindex(@c, @tmp)

    set  @tmp = (case when @t > 0 then substring(@tmp, @t + 1, len(@tmp) - @t) else '' end),
          @pos = @pos - 1  
  END --while
 
  return substring(@tmp + @c, 1, charindex(@c, @tmp) - 1)
END

Now you an use it as you want to.....

select dbo.getString(fieldname1, '-', 1) as field1, dbo.getString(fieldname1, '-', 2) as field2,... from yourtable

So sometimes it worth to work more until you have a library of functions and/or SP that will help you a lot...

good luck,
      xenon
0
 

Author Comment

by:mikeinoxford1
ID: 9607158
Thanks for the quick response.  All three have valid use and are excellent.  
 xenon_je's is great but the '-' will not be in the same position each time.  The left side of the string is not static.


I can use both PaulBarbin and  Dexstar solutions.  Thanks a million.  I will split the points between you both.

Take Care....Mike


0
 

Author Comment

by:mikeinoxford1
ID: 9607178
Looks like I can only select one answer.  Thank you all for your help and support.  

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9607184
You can do a split - I've never done it, but FYI.
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9608272
I think you can split the points after the fact if you ask the moderators to help you.

Post a comment in Community Support and tell them what you'd like to do, and include a link to this question so they know which one to deal with.

Community Support
http://www.experts-exchange.com/Community_Support/

This Question
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20775169.html

Glad you got it working,
Dex*
0
 

Author Comment

by:mikeinoxford1
ID: 9610198
Thanks for the great response.  I have requested the split of points.  We will see what happens. Thanks Again.  Built my uf  and modified my view.   Works like a champ.
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9610396
mikeinoxford1,

FYI, here is how to split the points:
http://www.experts-exchange.com/help/#13

Hope that helps,
Dex*
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9611212
Why do you say that my solution is not ok????

You stated:
"xenon_je's is great but the '-' will not be in the same position each time".....

My UDF doesn't states that the - character will be in the same position!!! Read again the function posted...It may contain some error because I don't have an sql server at hand to test.... but the ideea is good...

regards,
          xenon
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9618214
 I just installed my SQL Server...and made some tests...I had an error in the return statement.....

here is the final:

create function getString(@str varchar(250), @c char, @pos int)
returns varchar(100)
AS BEGIN

  declare @t int,
    @tmp varchar(250)

  set @t = 1
  select @tmp = @str

  while (@pos > 1) and (@t <> 0) BEGIN
    set @t = charindex(@c, @tmp)

    select  @tmp = (case when @t > 0 then substring(@tmp, @t + 1, len(@tmp) - @t) else '' end),
          @pos = @pos - 1  
  END --while
 
  return substring(@tmp + @c, 1, charindex(@c, @tmp + @c) - 1)
END
GO
--now lets test
select dbo.getString('abc-de-f', '-', 1), dbo.getString('abc-de-f', '-', 2), dbo.getString('abc-de-f', '-', 3)
--it will return
--abc      de      f

So as you can see it is working well...so make your tests yourself

xenon

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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