Silly Question

Question

Silly Question

Dave -310292

SSC-Addicted

Points: 440
More actions
July 11, 2006 at 8:16 am

#115763

I am trying to break a column of data (peoples names) into two new tables first Name and Last name.
I would like to write something that insert all of the information left of the first space into the first name table
then everything right of the first space into the last name table.
I am having trouble finding the location of the spaces in my query.

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Before we go into the actual query required to do this, I suggest that you think further about architecture. Surely you don't want separate first- and last-name tables? Separate columns within the same table would be the standard way forward here.

Anyway, assuming that you come round to agreeing - and have a table called 'names' containing a 'name' field (to be broken down) and 'firstname' and 'surname' fields (to be populated as you describe), something like this should do it:

update names

set

firstname = rtrim(left(name, charindex(' ', name))),

surname = rtrim(right(name, len(name) - charindex(' ', name))) from names

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dave -310292 SSC-Addicted Points: 440 More actions · Answer 2

Phil,

In this case unfortuanatly I am constrained by the application which I am using. While the architecture you suggest does make much more sense from an insert and IO perspective, unfortunately I do not have that capability.

The Charindex is exactly the function I was looking for and could not find. I should be fine from here.

Thanks a million

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Is the application 'home grown' or is it a package available on the 'open' market ? If it's a package available on the 'open' market then what is its name ?

I like to have the inside info in order to help my organization to steer clear of poorly designed and or written software.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

steve block SSCertifiable Points: 7698 More actions · Answer 4

You may want to do some further checking for last names that includes a space like "Mc Donald" or "O Brien". I believe a case statement can be used in the query for each exception you identify.

Steve