Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Divide String based on character.  In this case a -

Posted on 2003-10-22
14
Medium Priority
?
396 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 136 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 132 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 132 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
 
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

886 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