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?
 
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
0
 
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
0
 
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*
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
0
 
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


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

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

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

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.