We help IT Professionals succeed at work.

sql: separated one column into two columns

1,971 Views
Last Modified: 2008-01-09
I have a database table that has the data in 1 column that I need to separated into two columns and the changes to Title Case.

The data in the Colum contains the persons title separated by a hyphen then their full name
Sample data from the NAMECSO Colum
Select CSO_NAME  from tbl_members
SUPRTNDNT-JUDITH SMITH
SUPRTNDNT-JEFFREY A. WEAVER
SUPRTNDNT-STEVEN MAKER
SUPRTNDNT-THOMAS JOENS
ACTG SUPT-WILLIAM KARLA
ACTG SUPT-BOB SMITHE
ACTG SUPT-WILL SMITH
PRINCIPAL-JIMMY KIMMEL
PRINCIPAL-NICHOLAS CARTER
PRINCIPAL-WALTER CHROKITE
ASST SUPT-JOSEPH MCDERMIT
ASST SUPT-RONALD J GOODERAD
ASST SUPT-MARK ROSESMITH
ASST PRINCIPAL-KERRY LYNCH
ASST PRINCIPAL-KEITH GOLD
ASST PRINCIPAL-SALLY SMUNTERS
ACTG PRINL-DOUGLAS H. MCSTEEMEY
ACTG PRIN-RYAN R JAMES
ACTG PRIN-STUART SMALL
SUPRVSR-STEVEN WHITE
DIRECTOR-JOHN MORROWS
DIRECTOR-ANNE SPITZ

I would like to break them apart into 2 columns (title,name) so
SUPRTNDNT-JUDITH SMITH
SUPRTNDNT-JEFFREY A. WEAVER
Becomes:
Title             Name
SUPRTNDNT       Judith Smith
SUPRTNDNT       Jeffery A Weaver

I thought there was a way to do this with a mid string in sql.. but anything you can offer...

The database is in M$ Access, but I can put it in mysql or sql server if there is better support in those RDBMS.



Comment
Watch Question

Commented:
If you assume that you do not have anybody with a hyphenated last name then in MySQL this could be done by:
select substring_index(CSO_NAME, '-', 1), substring_index(CSO_NAME, '-', -1)  from tbl_members

Author

Commented:
what happens with people that have a hype in their name?

Author

Commented:
this is also in M$ Access and not mysql.. will it work on M$ Access or do i need to put it into mysql?

Author

Commented:
ok it does not work in M$ Access UnDefined Function substring_index

Commented:
With this query you would only get the last part of their name.  However if you use the following query it would substring based upon the position of the first hyphen in the string so it would not matter how many other hyphens there are in the string.

select left(CSO_NAME, locate('-', CSO_NAME)-1), substring(CSO_NAME, locate('-', CSO_NAME)+1) from tbl_members

Commented:
These functions only work in MySQL.

Author

Commented:
M$ Access Undefined function locate

Commented:
This is SQL:

Create the two new fields Title and Name in your table.  Then split the CSO_NAME by

UPDATE tbl_members SET Title = SUBSTRING_INDEX(CSO_NAME, '-', 1), Name = SUBSTRING_INDEX(CSO_NAME, '-', -1)

Then delete the CSO_NAME field if you don't need it any more.

Author

Commented:
well i guess ill  have to export it into mysql...

Commented:
Converting the names to a different case is a separate problem, and assuming you only need to do this once for your table, I'd suggest first separate the fields, then change the case in the Name field.

Changing the case would certainly be easiest with a simple php program.  Do you have access to php and MySQL?  If you put the table into SQL, then using php to do the conversions would be pretty simple.  

For example, in php, you would
(1) select each row,
(2) do strtolower() on the field, then
(3) explode() the field at the spaces into an array and
(4) do strtoupper() on the first character of each element of the array.  Lastly
(5) implode() back into a single string and
(6) update your Name field.

CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010

Commented:
luckynh,

BTW, my suggestion will falter on names like O'MALLEY or MCREYNOLDS; they will show
up as O'malley or Mcreynolds, respectively...

Regards,

Patrick

Author

Commented:
matthewspatrick:
could the same login be used to sperate/prepend  the zip code
i have the zipcode hass all the numbers together like this:
038269999
and i'd like to seperte them as 03820-9999
CERTIFIED EXPERT
Top Expert 2010

Commented:
luckynh,

I presume you mean the same "logic".

If that is what you meant, no--how could a function used to control letter case apply to zip codes?

In any event, one question per question, please...

Regards,

Patrick

Commented:
Hi luckynh,

In MySQL, you can also very easily separate the zipcode by

UPDATE tbl_members SET Zipcode = CONCAT(SUBSTRING(Zipcode FROM 1 FOR 5),'-',SUBSTRING(Zipcode FROM 6 FOR 4))

Commented:
By the way, Patrick is right about the O'Malley and McReynolds.  If you use my solution, you will also need to add another piece of the php logic to check for each of those and do the correct capitalization, but it's still very simple to do.

Author

Commented:
to clearify.... i wanted to concatnate the zipcode with a hyphen so i thought by using the InStr/MID/Left/Right i might be able to reuse part of what  was provided to do this.. yes i know about the 1 topic per question rule... thanks for reminding me...
as for the update statement you provided... it didnt work
syntax error missing operator in expression after FROM

        UPDATE tbl_members SET tbl_members.[Zip+4] = CONCAT(SUBSTRING([tbl_members].[Zip+4] FROM 1 FOR 5),'-',SUBSTRING([tbl_members].[Zip+4] FROM 6 FOR 4))
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.