Solved

Split one column into two

Posted on 2011-02-17
9
728 Views
Last Modified: 2012-05-11
Hi, I have a table that contains the following:

Term | Col | Department | Course | Course_Title

I need my Course Column split into two different columns Course and Course_Number. Examples for my course column include:

DAN 4640
FA  1040
MUED4120

So, some courses my have 1 space between the course and the number, while others have two spaces, or none. I'm not sure how to split these up so it will appear

Course | Course_Number
DAN        4640
FA           1040
MUED     4120

Any help would be greatly appreciated.
0
Comment
Question by:savache27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 34916820
Hi,

It seems you could use

select rtrim(left(Course, 4)) as Course, rtrim(right(Course, 4)) as Course_Number from yourtable

/peter
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 34916837
All course numbers are 4 digits length?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34916855
first format your string so that it only has one space.    Credit here: http://www.itjungle.com/fhg/fhg101106-story02.html for this part.  

then call fn_txt_split (code below)

SQL:

declare @test varchar(128)

set @test = 'this                test'

set @test =  replace(replace(replace(@test,' ','<>'),'><',''),'<>',' ')

select dbo.fn_txt_split(@test, " ")


/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 02/17/2011 10:18:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Txt_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Txt_Split]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 02/17/2011 10:18:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

GO

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 25

Expert Comment

by:Luis Pérez
ID: 34916893
If your course numbers are not always 4 digit length, you can try my snippet function.

Once the function created, you can do the following:
SELECT Course, dbo.getCourseNumber(Course) FROM myTable

Or to insert values into your new field:
UPDATE myTable SET Course_Number = dbo.getCourseNumber(Course)

Hope that helps.
CREATE FUNCTION getCourseNumber 
(
	@courseName varchar(100)
)
RETURNS INT
AS
BEGIN
	--First remove white spaces
	SET @courseName = REPLACE(@courseName, ' ', '')

	--Second split the numeric part
	WHILE ISNUMERIC(@courseName) = 0
		BEGIN
			SET @courseName = SUBSTRING(@courseName,2,LEN(@courseName)-1)
		END

	--Return the course number (casted as integer)
	RETURN CAST(@courseName AS INT)
END

Open in new window

0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34916903
sorry, the fn_txt_part was for multiple rows:

declare @test varchar(128)

set @test = 'this                test'

set @test =  replace(replace(replace(@test,' ','<>'),'><',''),'<>',' ')

select left(@test, charindex(' ', @test)-1) name, right(@test, len(@test)-charindex(' ', @test)) course

0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34917100
just realized you could also NOT have a space:

DECLARE @myString varchar(100);
DECLARE @course int;  
declare @name varchar(100)
SET @myString = 'DAN2456778'  
SET @course = reverse(LEFT( reverse(@mystring), PATINDEX('%[a-z]%',  reverse(@mystring)) - 1) )
set @name = replace(@mystring,@course,'')
select @name name, @course course
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34917131
or this
with c as (
select 'DAN 4640' as course
union
select 'FA  1040'
union
select 'MUED4120'
)
select c.course,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(course,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') cName,
ltrim(SUBSTRING(course, len(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(course,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''))+1, LEN(course))) cNo
from c

course	cName	cNo
DAN 4640	DAN 	4640
FA  1040	FA  	1040
MUED4120	MUED	4120

Open in new window

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34917178
or using a function

with c as (
select 'DAN 4640' as course
union
select 'FA  1040'
union
select 'MUED4120'
)
select c.course,
dbo.[RemoveNumeric](course) cName,
ltrim(SUBSTRING(course, len(dbo.[RemoveNumeric](course))+1, LEN(course))) cNo
from c

create function [dbo].[RemoveNumeric](
@s nvarchar(1000)  
) RETURNS nvarchar(1000)
begin
	return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@s,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','');
end;

Open in new window

0
 

Author Closing Comment

by:savache27
ID: 34917248
Thank you, Pivar!! That worked perfectly!! I really appreciate the help.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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