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

parse a column into several in SQL

Hello,

I have a sql 2000 table that has a column with values such as


First Name - George Last Name - Smith Room number - 229 Chart Location
- 7FRH.2
First Name - Mark Last Name - Johnson Room number - 976 Chart Location
- 4FV.5
First Name - Mary Last Name - Wesley Room number - 224 Chart Location
- 8BSS.3
First Name - Lucia Last Name - Guess Room number - 453 Chart Location
- 6BF.1
First Name - Charlotte Last Name - Lopez Room number - 229 Chart
Location - 4VW.5


I need to parse these values into columns.  From these columns I only
need the actual data and not the delimiters


the delimeters are:


"First Name - " 
" Last Name - " 
" Room number - " 
" Chart Location - " 


Therefore, I need to get 4 columns:


George  Smith   229     7FRH.2
Mark    Johnson 976     4FV.5
and so on..


Any ideas on how to parse these values into the 4 columns I need?


Thanks!
0
TheUndecider
Asked:
TheUndecider
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT SUBSTRING( ColumnName , charindex('-', ColumnName)+1, charindex('Last', ColumnName)-charindex('-', ColumnName)-1 ) as FistName,
       SUBSTRING( ColumnName , charindex('Last Name -', ColumnName)+11, charindex('Room', ColumnName)-charindex('Last Name -', ColumnName)-11 ) as LastName,
       SUBSTRING( ColumnName , charindex('Room number -', ColumnName)+13, charindex('Chart Location -', ColumnName)-charindex('Room number -', ColumnName)-13 ) as RoomNumber,
       RIGHT( ColumnName , LEN(ColumnName) - charindex('Chart Location -', ColumnName)-16 )ChartLocation
FROM yourTable
0
 
tigin44Commented:
here is a sample... you just replace the @var with your column Name...
declare @var varchar(1000)
set @var = 'First Name - George Last Name - Smith Room number - 229 Chart Location - 7FRH.2'
SELECT CHARINDEX(' Last Name - ', @var)

SELECT SUBSTRING(@var, 13, CHARINDEX(' Last Name - ', @var) - 13) AS firstName,
	   SUBSTRING(@var, CHARINDEX(' Last Name - ', @var) + 13, CHARINDEX(' Room number - ', @var) -(CHARINDEX(' Last Name - ', @var) + 13)) AS laststName, 
	   SUBSTRING(@var, CHARINDEX(' Room number - ', @var) + 15, CHARINDEX(' Chart Location - ', @var) -(CHARINDEX(' Room number - ', @var) + 15)) AS roomNumber, 
	   SUBSTRING(@var, CHARINDEX(' Chart Location - ', @var) + 18, LEN(@var) -(CHARINDEX(' Chart Location - ', @var) + 17)) AS charLocation

Open in new window

0
 
rajvjaCommented:
Hi,

   You can SSIS package to do this
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now