hoe do I extract? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

hoe do I extract?

Author

Topic

kifeda
Posting Yak Master

136 Posts

Posted - 2007-03-15 : 14:47:06

Hello All,

I have a table that has the first name and last name together in one field (stupid I know). I want to extract the first name from the field and copy it into another field names, well, firstname. I figure it might be possible since there is a comma after the last name. But how can I do this?

So for:

STRNAME
-------------
adams, john
-------------
washington, michael
-------------

I want it to be like this

STRNAME | STRFIRSTNAME
---------------------------
adams | john
---------------------------
washington | michael

So I guess there would be two parts to the statement:

1. First I need to select or trim the strname field to the comma. BUT I don't want to just trim it- I want to copy it.

2. The next thing I need to do is to copy the lastname (the part that I trimmed) into a new field which I created called lastname.

Any help would be greatly appreciated!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 16:56:34

You said you want to copy the last name to a new field named lastname, but you weren't really clear about the first name, you show STRFIRSTNAME, but I'm not sure why you'd name two columns in the same table so differently?

Anyhow, here's an update statement that will leave the existing column (STRNAME) alone and update two columns named firstname and lastname with the appropriate values

update yourtable
set lastname = left(STRNAME, charindex(',', STRNAME) - 1), 
firstname = ltrim(substring(STRNAME, charindex(',', STRNAME) + 1, 100))

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-14 : 10:37:28

this did not work

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 11:01:02

SELECT dbo.fnParseString(-1, ',', StrName) AS LastName,
dbo.fnParseString(1, ',', StrName) AS FirstName
FROM Table1

Find second version of fnParseString found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 13:55:41

[code]
select
FirstName = left(parsename(replace(a.name,', ','.'),1),30),
LastName = left(parsename(replace(a.name,', ','.'),2),30)
from
(
-- Test Data
select name = 'adams, john' union all
select name = 'washington, michael'
) a

Results:

FirstName LastName
------------------------------ ------------------------------
john adams
michael washington
[/code]

CODO ERGO SUM

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-14 : 14:27:37

I'm a bit confused Michael. can you explain what you wrote using the table name sheet3$. I don't see the name of the table in the example you gave. Remember I'm still kind of new to this!

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-14 : 14:31:11

Peso your exampel did not work either. I just changed the table name and executed the query. This is what I submitted:

SELECT dbo.fnParseString(- 1, ',', strname) AS LastName, dbo.fnParseString(1, ',', strname) AS FirstName
FROM Sheet3$

the error I got said invalid object named 'dbo.fnParseString'

What should I do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 14:48:48

Sheet3$ ???

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 15:12:29

quote:
Originally posted by kifeda

I'm a bit confused Michael. can you explain what you wrote using the table name sheet3$. I don't see the name of the table in the example you gave. Remember I'm still kind of new to this!

You should read about Derived Tables in SQL Server Books Online:
http://msdn2.microsoft.com/en-us/library/ms177634.aspx

CODO ERGO SUM

kifeda
Posting Yak Master

136 Posts

Posted - 2007-11-14 : 16:16:37

sheet3# was the name of the table. I am happy to say that I was able to figure it out thanks to the example that you all gave me. Thanks!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 16:36:46

sheet3$ would be the default resulting name of an excel sheet( named "sheet3" of course) to a server.

In excel, you could have just clicked the column, chosen Text to Columns and specifed a column delimiter. Then it would have imported two columns.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources