How to split info based on a space

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

How to split info based on a space

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 11:35:38

I have a firstname and lastname column. Somehow, some of the names in the firstname column have both the first and last name in them seperated by a space ex. Joe Bins. What is the query that would take the last name and funnel it into the lastname field?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 11:54:31

Are you sure there are no other examples?
Like firstname middlename lastname?

Are you sure there is always only one space between first and last name?

Are you sure there are no other seperators like / for example?

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 11:59:05

Yes I am 100% sure. about half of the records have first name _ last name ex. Joe Bins. While the other half have Joe.

And I know there are no spaces after because I have trimmed the column.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:09:31

[code]declare @SampleTable table (firstname varchar(255), lastname varchar(255))
insert @SampleTable
select 'Joe Bins',null union all
select 'Web','Fred'

select * from @SampleTable
-- now we have testdata!
update @SampleTable
set firstname = parsename(replace(firstname,' ','.'),2),
lastname = parsename(replace(firstname,' ','.'),1)
where firstname like '%[a-z] [a-z]%'

-- show the result
select * from @SampleTable[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 12:27:30

Did I enter this right?

DECLARE @fixdata fixdata(F1 varchar(255), F2 varchar(255)) INSERT @fixdata
SELECT 'Joe Bins', NULL
UNION ALL
SELECT 'Web', 'Fred'
SELECT *
FROM @fixdata
UPDATE @fixdata
SET F1 = parsename(replace(F1, ' ', '.'), 2), F2 = parsename(replace(F2, ' ', '.'), 1)
WHERE F1 LIKE '%[a-z] [a-z]%'
SELECT *
FROM @fixdata

fixdata is the table name
F1 = last name
F2 = first name

I am getting an error with this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:30:25

quote:
Originally posted by jatrix32

Did I enter this right?

DECLARE @fixdata fixdata(F1 varchar(255), F2 varchar(255)) INSERT @fixdata
SELECT 'Joe Bins', NULL
UNION ALL
SELECT 'Web', 'Fred'
SELECT *
FROM @fixdata
UPDATE @fixdata
SET F1 = parsename(replace(F1, ' ', '.'), 2), F2 = parsename(replace(F1, ' ', '.'), 1)
WHERE F1 LIKE '%[a-z] [a-z]%'
SELECT *
FROM @fixdata

fixdata is the table name
F1 = last name
F2 = first name

I am getting an error with this.

See your problem in red
And - what is the error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 12:32:48

I fixed the part in red and still got the following error:

Line 1: Incorrect Syntax near 'F1'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:37:27

DECLARE @fixdata ~~fixdata~~ table(F1 varchar(255), F2 varchar(255))

But I think you have misunderstood!

My example has nothing to do with your existing table.
You can copy and paste my example only to see what happens.

For your REAL table it would be like this:

update fixdata
SET F1 = parsename(replace(F1, ' ', '.'), 2), 
    F2 = parsename(replace(F1, ' ', '.'), 1)
WHERE F1 LIKE '%[a-z] [a-z]%'

But you should test it in dev environment!!

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 12:42:21

I am in a dev environment

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 12:45:05

Now it ran, but it only affected 2 rows.

Does that have something to do with this:

SELECT 'Joe Bins', NULL
UNION ALL
SELECT 'Web', 'Fred'

I am a newbie to SQL, so should those be hard coded like that?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:51:21

Then there were only two records with "FirstnameSPACELastname" in F1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 12:52:03

Nothing to do with hard coded!
Show the statement that you have executed.

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 12:54:22

DECLARE @fixdata TABLE (F1 varchar(255), F2 varchar(255)) INSERT @fixdata
SELECT 'Joe Bins', NULL
UNION ALL
SELECT 'Web', 'Fred'
SELECT *
FROM @fixdata
UPDATE @fixdata
SET F1 = parsename(replace(F1, ' ', '.'), 2), F2 = parsename(replace(F2, ' ', '.'), 1)
WHERE F1 LIKE '%[a-z] [a-z]%'
SELECT *
FROM @fixdata

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-12 : 13:02:05

Ok that is the EXAMPLE!
That has NOTHING to do with your real data.

I've posted already:


update fixdata
SET F1 = parsename(replace(F1, ' ', '.'), 2), 
    F2 = parsename(replace(F1, ' ', '.'), 1)
WHERE F1 LIKE '%[a-z] [a-z]%'

No, you're never too old to Yak'n'Roll if you're too young to die.

jatrix32
Starting Member

49 Posts

Posted - 2010-10-12 : 13:12:26

Should F1 be after replace in both criteria?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-10-12 : 13:47:30

quote:
Originally posted by jatrix32

Should F1 be after replace in both criteria?

All you need to do is run the code webfred gave you:

update @SampleTable
set firstname = parsename(replace(firstname,' ','.'),2),
    lastname =  parsename(replace(firstname,' ','.'),1)
where firstname like '%[a-z] [a-z]%'

You need to replace @SampleTable with your actual table name.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources