Solved

Divide String based on character.  In this case a -

Posted on 2003-10-22
14
374 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

832 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