Splitting one column into two columns using a delimeter

Hi,

I need to split the data of one column into two and have it output on the same row.
I'm using SQL Server 2000.
Table1
Data from column 1 = 10/5

Table1
Output = new column 1 = 10 , new column 2 = 5

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

bertmnAuthor Commented:
Also needs to not break if column is NULL
0
BrandonGalderisiCommented:
You use a combination of case and charindex.

The first column will either be the FIRST part of the column1 data or all of the data.  And the second column will contain the second part if the delimiter is in there, and NULL if not.
select column1,
  case when charindex('/', Column1)>0 
              then left(column1,charindex('/',column1)-1)
           else column1
  end,
  case when charindex('/', Column1)>0 
              then right(column1,len(column1)-charindex('/',column1))
          else NULL
  end
From 1

Open in new window

0

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
bertmnAuthor Commented:
Ok its partially working. Column 2 has no data on a working split and shows NULL when the value is empty, which is fine for the NULL part.
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.

bertmnAuthor Commented:
Here is what I am using.
select USCATVLS_3,
  case when charindex('/', USCATVLS_3)>0
              then left(USCATVLS_3,charindex('/',USCATVLS_3)-1)
           
  end AS Value1,
  case when charindex('/', USCATVLS_3)>0
              then right(USCATVLS_3,len(USCATVLS_3)-charindex('/',USCATVLS_3))
          else NULL
  end AS Value2
From Table1

USCATVLS_3 = 30/10
Value1 = 30
Value2 = empty <-- should be 10
0
jamesguCommented:
BG's solution seems good,

what did you mean by partially working? can you give some examples?
0
bertmnAuthor Commented:
select USCATVLS_3,
  case when charindex('/', USCATVLS_3)>0
              then left(USCATVLS_3,charindex('/',USCATVLS_3)-1)
            else USCATVLS_3
  end AS Value1,
  case when charindex('/', USCATVLS_3)>0
              then right(USCATVLS_3,len(USCATVLS_3)-charindex('/',USCATVLS_3))
          else NULL
  end AS Value2
From Table1

Output:
USCATVLS_3 = 30/10
Value1 = 30
Value2 = empty <-- should be 10
0
jamesguCommented:
sorry, bertmn

didn't see your post

it works when i tried the below code

declare @USCATVLS_3 varchar(20)
begin
set @USCATVLS_3='30/10'
select @USCATVLS_3,
  case when charindex('/', @USCATVLS_3)>0 
              then left(@USCATVLS_3,charindex('/',@USCATVLS_3)-1)
            
  end AS Value1,
  case when charindex('/', @USCATVLS_3)>0 
              then right(@USCATVLS_3,len(@USCATVLS_3)-charindex('/',@USCATVLS_3))
          else NULL
  end AS Value2
end;

Open in new window

0
bertmnAuthor Commented:
True it works when I try your code but as soon as I change it like this:


Value 2 is empty again. Would it have any issues that USCATVLS_3 is char(11)?
select USCATVLS_3,
  case when charindex('/', USCATVLS_3)>0 
              then left(USCATVLS_3,charindex('/',USCATVLS_3)-1)            
  end AS Value1,
  case when charindex('/', USCATVLS_3)>0 
              then right(USCATVLS_3,len(USCATVLS_3)-charindex('/',USCATVLS_3))
          else NULL
  end AS Value2 FROM Table1

Open in new window

0
bertmnAuthor Commented:
YES! I tried your code again declaring USCATVLS_3 as char(11) and it broke Value2 to empty.
0
jamesguCommented:
yes, you were right, it's because of char(11),

for char columns, there is some spaces on the right to fill the string to the length(11 in this case), while for varchar column, it doesn't

you need to trim the space on the right, try this

then right(rtrim(@USCATVLS_3),len(rtrim(@USCATVLS_3))-charindex('/',@USCATVLS_3))
               ^^^^^                            ^^^^
declare @USCATVLS_3 char(11)
begin
set @USCATVLS_3='30/10'
select @USCATVLS_3,
  case when charindex('/', @USCATVLS_3)>0 
              then left(@USCATVLS_3,charindex('/',@USCATVLS_3)-1)
            
  end AS Value1,
  case when charindex('/', @USCATVLS_3)>0 
              then right(rtrim(@USCATVLS_3),len(rtrim(@USCATVLS_3))-charindex('/',@USCATVLS_3))
          else NULL
  end AS Value2
end;

Open in new window

0
bertmnAuthor Commented:
That works, Thank you. Now on to adding that to the full query. Next is to add those Values to another column. There might be another question involved later. :) Thanks. I will accept both codes as they both worked.
0
jamesguCommented:
bertmn, it was Brandon's solution, you may want to grant the points to him

glad it works for you.
0
bertmnAuthor Commented:
Oh ****, Can I give him points after I accepted it?
0
BrandonGalderisiCommented:
You would have to post in community support to have the question re-opened.
0
BrandonGalderisiCommented:
thank you jamesgu and bertmn.
0
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.

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.