| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-18-07, 11:07
|
|
Registered User
|
|
Join Date: May 2007
Posts: 3
|
|
|
Substring
|
|
I want to split "name" column into 3 columns......"lastname" "firstname" "mi" in SQL. Following is the name format....
Abbott,Gregory A.
Abel,Guy C.
Adams,Daniel Lee
Adams,Doris Jean
Adams,Kevin B.
Adams,Lewis Kevin
Adamson Jr.,Gerald G.
Aderhold,R. Chuck
Alden,Bradley Douglas
Aldrich,Daniel J.
Can anybody help me....
|
|
05-18-07, 11:15
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
Split on comma, then split on space...
|
|
05-18-07, 11:32
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
|
|
for a more specific solution, you will want to let us know which database system you're using -- postgresql, sybase, db2, whatever -- so that we can move this thread to the appropriate forum
|
|
05-18-07, 11:38
|
|
Registered User
|
|
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
|
|
Code:
SELECT
SUBSTR(name,1,POSITION(',' IN name)-1) as lastname
,SUBSTR(
SUBSTR(name,
POSITION(',' IN name)+1,
LENGTH(name)),
1,POSITION(' ' IN
SUBSTR(name,
POSITION(',' IN name)+1,
LENGTH(name))
)-1
) AS firstname
,SUBSTR(
SUBSTR(name,
POSITION(',' IN name)+1,
LENGTH(name)),
POSITION(' ' IN
SUBSTR(name,
POSITION(',' IN name)+1,
LENGTH(name))
),LENGTH(name)-
POSITION(' ' IN name)+1
) AS middlename
|
|
05-18-07, 18:06
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
|
|
05-18-07, 19:42
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
assuming, mister blindman sir, that the original poster is running some version of microsoft sql server
that's a fair guess, given that so many people cannot tell the difference between microsft sql server as a dbms product and sql as a language
but this is the sql forum, and not the microsoft sql server forum, so you gots to take that into consideration
charindex, indeed 
|
|
05-19-07, 00:12
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
I have a version for Access as well, and a buddy of mine converted the logic to .NET.
__________________
If it's not practically useful, then it's practically useless.
blindman 
www.chess.com: "sqlblindman"
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Substring
