Parse a colum into a temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parse a colum into a temp table?

Author

Topic

loiter99
Starting Member

38 Posts

Posted - 2005-01-25 : 11:38:54

Hello,
I have a column I would like to use to populate a temp table, but it needs to be split out. Here is how the data appears:

|name |
---------------------
|SMITH, TIMMY/19244 |
|JONES, JOHH/12345 |

I would like to dump it to a temp table with the first and last name split out and drop the ID.

Could someone plz help me out?

TIA.
J

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-25 : 11:59:54

here ya go:


Declare @myTable table (name varchar(1000))
Insert Into @myTable 
Select 'SMITH, TIMMY/19244'
Union All Select 'JONES, JOHH/12345'

Select 
	firstName = ltrim(rtrim(substring(name,charindex(',',name)+1,charindex('/',name)-charindex(',',name)-1))),
	LastName = Left(name,charindex(',',name)-1)
--	,ID = right(name,charindex('/',reverse(name))-1)
From @myTable

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

loiter99
Starting Member

38 Posts

Posted - 2005-01-25 : 12:05:55

Fan-Tas-Tic !!!!

J

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 06:03:29

another way with less functions using Corey's examples.


Select 	firstName = parsename(replace(replace(name, '/', '.'), ', ', '.'), 2),
	LastName = parsename(replace(replace(name, '/', '.'), ', ', '.'), 3)
--	,ID = parsename(replace(replace(name, '/', '.'), ', ', '.'), 1)
From @myTable

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-26 : 07:50:46

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources