How to do a Column Split in SQL

[gfranklin]

I have a column in my sql table that contains 2 different values, I would like to separate them into 2 different columns! How do I go about doing this? I'm a beginner to sql so my knowledge is very limited! Thanks in advance for your help!

Gabby

 

[SQLBill]

It will help if you provide an example of what the data in the column looks like and what you would like it to look like.

-SQLBill

 

[gfranklin]

SQLBill,

Here is an example of what the data in that column looks like! rn-123567 I would like to split it into 2 separate columns and drop the - if possible.

Thanks for you help! Gabby

 

[ThomVF]

Add the new column.

Next, populate the new column and adjust the original/old column:

update <table>
set new_col = substring(old_col,charindex(old_col,'-')+1)
, old_col = substring(old_col,1,charindex(old_col,'-')-1)
; &quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[gfranklin]

Thomas,

Remember I'm new to SQL so when you say to &quot;populate the new column and adjust the original/old column&quot; I'm not
really sure where I'm suppose to go or what to do. Can you give me detailed instructions Please?

Thanks, Gabby

 

[ThomVF]

What I meant was, this UPDATE statement below will populate the new and adjust the old:

update <table>
set new_col = substring(old_col,charindex(old_col,'-')+1)
, old_col = substring(old_col,1,charindex(old_col,'-')-1)
;

** Substitute your actual table name for <table name>
** Substitute the column currently containing the data for &quot;old_col&quot;
** Substitute the newly added column for &quot;new_col&quot;

You would create the new column using Enterprise Manager (or ask a DBA if you have one).

You would run the UPDATE statement in Query Analyzer.
&quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[gfranklin]

Thomas,

This is the message I get when I run it!

Server: Msg 174, Level 15, State 1, Line 2
The substring function requires 3 arguments.

update CallLog
set test= substring(cause,charindex(cause,'-')+1)
, cause = substring(cause,1,charindex(cause,'-')-1)
;

Where did I go wrong?

Thanks, Gabby

 

[ThomVF]

Sorry, the 3rd argument (length) is now required, my mistake. Also, I had the order of the argument backwards in the CHARINDEX functions...

update CallLog

set test= substring(cause,
charindex('-',cause)+1,
LEN(cause) - charindex('-',cause)
)

, cause = substring(cause,1,charindex('-',cause)-1)
;
&quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com