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

Split one column into two

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
savache27
Asked:
savache27
  • 3
  • 2
  • 2
  • +2
1 Solution
 
pivarCommented:
Hi,

It seems you could use

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

/peter
0
 
Luis PérezSoftware Architect in .NetCommented:
All course numbers are 4 digits length?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Luis PérezSoftware Architect in .NetCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
savache27Author Commented:
Thank you, Pivar!! That worked perfectly!! I really appreciate the help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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