| Author |
 Topic  |
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
 Posted - 07/22/2009 : 07:57:41
|
Hi all,
I have a col as name which includes full name as below
Name
-----------
Maloe,Suzan M
Conn,Christopher Z
Van Pess,Wen P
aak, cse
while inserting in to other table from the above name, I need to split it into LastName,FirstName,MilddleName ( Maloe=LastName Suzan=FirstName M=Middlenname).
Can any one help me in writing a select query to split this.
Regards,
aak
|
Edited by - aakcse on 07/22/2009 11:11:27
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/22/2009 : 08:03:13
|
select
name
,LTRIM(RTRIM(Substring(Name,1,Patindex('%,%',Name)-1))) lastName
,LTRIM(RTRIM(Substring(Name,Patindex('%,%',Name)+1,len(name)))) fristName
,NULL
from table
the above works up to some extent however getting middle name and cleaning firstname has to be done..
Regards,
aak
|
Edited by - aakcse on 07/22/2009 08:05:50 |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 07/22/2009 : 08:20:53
|
If you try "first last and middle name" in google custom search in the right upper corner of this forum then you will get some solutions.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 07/22/2009 : 09:28:31
|
Hi aakcse
Try this...
CREATE TABLE #temp
(
IND INT IDENTITY(1,1),
ENAME VARCHAR(100)
)--Length its upto Req...
INSERT
INTO #temp
SELECT 'Maloe, Suzan M'
UNION ALL
SELECT 'Conn, Christopher Z'
UNION ALL
SELECT 'Van, PessWen P'
UNION ALL
SELECT 'Asif, Khan'
SELECT LEFT(ENAME,CHARINDEX(',',ENAME)-1) ,
SUBSTRING(ENAME,CHARINDEX(',',ENAME)+1,CHARINDEX(' ',ENAME)),
CASE
WHEN CHARINDEX(' ',REPLACE(ENAME,', ','##'))>0
THEN LTRIM(SUBSTRING(ENAME,CHARINDEX(' ',REPLACE(ENAME,', ','##')),2))
ELSE ''
END
FROM #TEMP
-------------------------
R.. |
Edited by - rajdaksha on 07/22/2009 09:30:57 |
|
|
|
Mangal Pardeshi
Posting Yak Master
India
110 Posts |
Posted - 07/22/2009 : 09:33:22
|
DECLARE @t TABLE
(Names VARCHAR(100))
INSERT INTO @T SELECT
'Maloe,Suzan M' UNION ALL SELECT
'Conn,Christopher Z' UNION ALL SELECT
'Van Pess,Wen P' UNION ALL SELECT
'Van Pess,TEST' UNION ALL SELECT
'Asif,Khan'
SELECT LEFT(Names,CHARINDEX(',',NAMES)-1) as FirstName
,CASE WHEN CHARINDEX(' ',REVERSE(names))=0 OR
CHARINDEX(' ',REVERSE(names))> CHARINDEX(',',REVERSE(names))
THEN NULL
ELSE LEFT(REVERSE(NAMES),CHARINDEX(' ',REVERSE(NAMES))-1) END AS MiddleName
,CASE CHARINDEX(' ',SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names)))
WHEN 0
THEN SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names))
ELSE LEFT(SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names)), CHARINDEX(' ',SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names))))
END LastName
FROM @T
Mangal Pardeshi
http://mangalpardeshi.blogspot.com |
|
|
|
aakcse
Aged Yak Warrior
India
517 Posts |
Posted - 07/22/2009 : 11:01:44
|
Mangal Thank you so much, this logic is too good..
Thanks rajdaksha and Others all.
CASE CHARINDEX(' ',SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name)))
WHEN 0
THEN SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name))
ELSE LEFT(SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name)), CHARINDEX(' ',SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name))))
END FirstName
,LTRIM(RTRIM(LEFT(Name,CHARINDEX(',',NAME)-1))) as LastName
,CASE WHEN CHARINDEX(' ',REVERSE(name))=0 OR
CHARINDEX(' ',REVERSE(name))> CHARINDEX(',',REVERSE(name))
THEN NULL
ELSE LEFT(REVERSE(NAME),CHARINDEX(' ',REVERSE(NAME))-1)
END AS MiddleName
|
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 07/22/2009 : 11:47:54
|
This function has been tried and tested over a decade:
http://sqlblindman.pastebin.com/f68f37c15
________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________ |
|
|
|
blackair
Starting Member
USA
1 Posts |
Posted - 10/07/2010 : 21:43:01
|
LEFT(Name,CHARINDEX(' ',NAME)-1) as FirstName
,CASE
WHEN CHARINDEX(' ',REVERSE(Name))+ CHARINDEX(' ',NAME) < LEN(Name)
THEN SubString(Name, CHARINDEX(' ', (Name))+1, (LEN(Name)-CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ',Name)))
Else 'Null'
END AS MiddleName
,RIGHT(Name, CHARINDEX(' ', REVERSE(NAME))-1 ) AS LastName |
|
|
|
figmo
Starting Member
18 Posts |
Posted - 04/27/2011 : 13:29:28
|
Thanks for the suggestions. What I ended up doing is porting the code from my custom function to inline in the select statement and that made HUGE improvements in my query time. But damn.....is it ever UGLY! Below is the part of my select statement that does the name manipulation:
CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
Artist
ELSE
CASE WHEN CHARINDEX('&', SUBSTRING(Artist,CHARINDEX(', ', Artist),LEN(ARTIST))) < 1 THEN
SUBSTRING(Artist, CHARINDEX(', ', Artist)+2, LEN(ARTIST))
ELSE
LEFT(SUBSTRING(Artist, CHARINDEX(', ', Artist)+2, LEN(ARTIST)), CHARINDEX('&',SUBSTRING(Artist,CHARINDEX(', ', Artist)+2,LEN(ARTIST)))-1)
END
END + ' ' +
CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
''
ELSE
SUBSTRING(Artist, 0, CHARINDEX(', ', Artist))
END + ' ' +
CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
''
ELSE
CASE WHEN CHARINDEX('&', Artist) < 1 THEN
''
ELSE
SUBSTRING(Artist, CHARINDEX('&', Artist), LEN(ARTIST))
END
END
Question: Is there anyway to create a temp variable in the select statement? In my function (or stored procedures) I can DECLARE a var, and then SET it. This is useful for remembering parts of the algorithm that are used more than once (e.g. an int holding the position of the comma character). Without this, I have to repeat these CHARINDEX calls each time I need that value.
My performance with the above is pretty good - but always looking to tweak it even faster. |
|
|
|
nikita2010
Starting Member
1 Posts |
Posted - 07/13/2012 : 11:55:14
|
Hello All,
I have a requirement where the name field had both first and last name. Some seperated with spaces and some with comma. If they are seperated with comma, anything before , goes to lastname and after that is firstname. If it is seperated by space, anything before first space goes to firstname and after the first space is lastname.
Any help is much appreciated.
Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/14/2012 : 00:01:49
|
quote:
Originally posted by nikita2010
Hello All,
I have a requirement where the name field had both first and last name. Some seperated with spaces and some with comma. If they are seperated with comma, anything before , goes to lastname and after that is firstname. If it is seperated by space, anything before first space goes to firstname and after the first space is lastname.
Any help is much appreciated.
Thanks.
SELECT CASE WHEN CHARINDEX(' ',Col)>0 THEN PARSENAME(REPLACE(Col,' ','.'),2) WHEN CHARINDEX(',',Col) > 0 THEN PARSENAME(REPLACE(Col,',','.'),1) END AS FirstName,
CASE WHEN CHARINDEX(' ',Col)>0 THEN PARSENAME(REPLACE(Col,' ','.'),1) WHEN CHARINDEX(',',Col) > 0 THEN PARSENAME(REPLACE(Col,',','.'),2) END AS LastName
FROM Table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
query to separate first last and middle name
