Divide String based on character. In this case a -

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.
mikeinoxford1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PaulBarbinCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PaulBarbinCommented:
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
DexstarCommented:
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*
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

xenon_jeCommented:
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
mikeinoxford1Author Commented:
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


mikeinoxford1Author Commented:
Looks like I can only select one answer.  Thank you all for your help and support.  

PaulBarbinCommented:
You can do a split - I've never done it, but FYI.
DexstarCommented:
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*
mikeinoxford1Author Commented:
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.
DexstarCommented:
mikeinoxford1,

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

Hope that helps,
Dex*
xenon_jeCommented:
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
xenon_jeCommented:
 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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.