• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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
0
bertmn
Asked:
bertmn
  • 8
  • 4
  • 3
2 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now