SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Break up full name col into fname, lname cols


Break up full name col into fname, lname cols

Author
Message
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9562 Visits: 2048
PARSENAME looks cool for IP ADDRESSES and for this particular case may be useful it is an example on how to use a simple function for uninteded purposes!



* Noel
scotttr
scotttr
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1
Hey Jonathan,
ThE PARSENAME() solution looks good. I'm validating the results. Thanks everyone for the contributions.



jhklink
jhklink
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 222
Here is some code I have used in the past:


Update Names
Set FullName = RTRIM(FullName)

Update Names
Set FullName = LTRIM(FullName)

Update Names
Set FullName = REPLACE(FullName, ' ', '!')
Where FullName LIKE '% %'

Select FullName as FullName_Original, FullName, CHARINDEX('!', FullName, 1) as Posititon
into #Temp
From Names

Select FullName, Left(FullName,Posititon)
as Name1
Into #Temp2
From #Temp

Update #Temp
Set FullName = REPLACE(#Temp.FullName, #Temp2.Name1, '')
From #Temp, #Temp2
Where #Temp.FullName = #Temp2.FullName

Update #Temp2
Set Name1 = REPLACE(Name1, '!', '')
Where Right(Name1,1) = '!'

Update Names
Set Names.FirstName = #Temp2.Name1
From Names, #Temp2
Where Names.FullName = #Temp2.FullName

Update Names
Set FullName = REPLACE(FullName, '!', ' ')
Where FullName LIKE '%!%'

Drop Table #Temp
Drop Table #Temp2

Then just repeat the steps for Name2 & Name3



Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3794 Visits: 1464
You don't say anything about the quality of the name fields. If they're clean, the PARSENAME function is a great approach.

I work with a lot of crappy name & address info we get from clients, and I would be careful assuming it doesn't already have periods that would screw up the PARSENAME approach. Any "Mr." or "Mrs." or "Dr." prefixes in these names?

I've used LEFT(xx,CHARINDEX(' ',xx)-1) to get the first word in a text field. I've also used RIGHT(xx,CHARINDEX(' ',REVERSE(xx))-1) to get the last word. Use "WHERE CHARINDEX(' ',xx) > 0' if you're not sure there is more than one word in the field.

(Mostly to build tables to see what new prefixes and suffixes they've come up with this month)



gljjr
gljjr
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 1
For this type of problem I typically use a UDF. The one I'm providing here will return a table for each record. If you are only using it for a specific purpose you could certainly modify it to just return the full word for a given location. IE: 1st, 2nd, or last word.

 
CREATE FUNCTION dbo.f_ParseDelimitedList
(
@ID int -- Used so we can link the resulting table in a query to some value
, @delimitedList nvarchar(3000) -- The list of items to parse out.
, @Delimiter nchar(1) = ',' -- The delimiter used. Defaults to a comma.
)
RETURNS @tbl TABLE (ID int ,ObjID int , FieldValue nvarchar(260))
BEGIN
DECLARE
@CounterID nvarchar(4)
, @FieldValue nvarchar(260)
DECLARE @tmpTable TABLE (ID int IDENTITY(1,1),ObjID int , FieldValue nvarchar(260))
WHILE CharIndex(@Delimiter, @delimitedList) > 0
BEGIN
SET @FieldValue = LTrim(RTrim(SUBSTRING(@delimitedList, 1, charIndex(@Delimiter, @delimitedList)-1)))
INSERT INTO @tmpTable (ObjID, FieldValue)
SELECT @ID, @FieldValue
SET @delimitedList = LTrim(RTrim(SUBSTRING(@delimitedList, (charIndex(@Delimiter, @delimitedList) + 1), Len(@delimitedList))))
END
IF LTrim(RTrim(@delimitedList)) != ''
INSERT INTO @tmpTable (ObjID, FieldValue)
SELECT @ID, @delimitedList
INSERT INTO @tbl SELECT * FROM @tmpTable
RETURN
END
/* Usage
Select *
From dbo.f_ParseDelimitedList(1,'Apples,Oranges',',')
*/



Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

Edited by - gljjr on 11/17/2003 7:23:51 PM




Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Jonathan
Jonathan
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1257 Visits: 54
quote:

You don't say anything about the quality of

I work with a lot of crappy name & address info we get from clients, and I would be careful assuming it doesn't already have periods that would screw up the PARSENAME the name fields. If they're clean, the PARSENAME function is a great approach.approach. Any "Mr." or "Mrs." or "Dr." prefixes in these names?


You can use REPLACE a few times with PARSENAME to take case of periods in the data.

Again, any approach will almost certainly give some bad results unless the data is oversimplified. As I wrote previously, my last name is two words; I don't appreciate people misspelling my name just so it fits their model of how names should be spelled (i.e. with no spaces). As Frank mentioned, the real issue here is that the schema was not normalized, and now you're attempting to fix it without all the necessary information.

--Jonathan



--Jonathan
vadba
vadba
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1306 Visits: 461
[quote]
For this type of problem I typically use a UDF. The one I'm providing here will return a table for each record. If you are only using it for a specific purpose you could certainly modify it to just return the full word for a given location. IE: 1st, 2nd, or last word.

I did just that, and this is what I came up with:

GO
DROP FUNCTION dbo.f_ParseDelimitedListPart
GO
CREATE FUNCTION dbo.f_ParseDelimitedListPart
(
@ID int, -- Used so we can link the resulting table in a query to some value
@delimitedList nvarchar(3000), -- The list of items to parse out.
@Delimiter nvarchar(10) = ',' , -- The delimiter used. Defaults to a comma.
@listpart int
)
RETURNS nvarchar(3000)

BEGIN
DECLARE @FieldValue nvarchar(260), @string nvarchar(3000)
DECLARE @loopCnt int, @delimLength int

SET @loopCnt = 0
SET @delimLength = (SELECT Len(@Delimiter))
SET @string = ''

WHILE CharIndex(@Delimiter, @delimitedList) > 0
BEGIN
SET @loopCnt = @loopCnt + 1

SET @FieldValue = LTrim(RTrim(SUBSTRING(@delimitedList, 1, charIndex(@Delimiter, @delimitedList)-1)))

IF @listpart = @loopCnt
RETURN @FieldValue

SET @delimitedList = LTrim(RTrim(SUBSTRING(@delimitedList, (charIndex(@Delimiter, @delimitedList) + @delimLength), Len(@delimitedList))))
END

IF @listpart = @loopCnt + 1
SET @string = @delimitedList
ELSE
SET @string = ''

RETURN @string
END

-- EXAMPLES

/*
Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges', ',', 1)
Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 2)
Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 3)
Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 4)
Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 5)


Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges', '//', 1)
Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 2)
Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 3)
Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 4)

*/



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search