Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1909
  • Last Modified:

sql: separated one column into two columns

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.



0
Timothy Golden
Asked:
Timothy Golden
  • 7
  • 4
  • 3
  • +1
1 Solution
 
BinkersCommented:
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
0
 
Timothy GoldenBrand AmbassadorAuthor Commented:
what happens with people that have a hype in their name?
0
 
Timothy GoldenBrand AmbassadorAuthor 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?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Timothy GoldenBrand AmbassadorAuthor Commented:
ok it does not work in M$ Access UnDefined Function substring_index
0
 
BinkersCommented:
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
0
 
BinkersCommented:
These functions only work in MySQL.
0
 
Timothy GoldenBrand AmbassadorAuthor Commented:
M$ Access Undefined function locate
0
 
Cornelia YoderArtistCommented:
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.

0
 
Timothy GoldenBrand AmbassadorAuthor Commented:
well i guess ill  have to export it into mysql...
0
 
Cornelia YoderArtistCommented:
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.

0
 
Patrick MatthewsCommented:
Hello luckynh,

If you keep everything in Access, *and* all entries have at least one hyphen, *and* the first
hyphen always delimits the title and name...

SELECT Left([NAMECSO], InStr(1, [NAMECSO], "-") - 1) AS Title,
    StrConv(Mid([NAMECSO], InStr(1, [NAMECSO], "-") + 1), 3) AS [Name]
FROM SomeTable

Regards,

Patrick
0
 
Patrick MatthewsCommented:
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
0
 
Timothy GoldenBrand AmbassadorAuthor 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
0
 
Patrick MatthewsCommented:
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
0
 
Cornelia YoderArtistCommented:
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))
0
 
Cornelia YoderArtistCommented:
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.
0
 
Timothy GoldenBrand AmbassadorAuthor 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))
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now