Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Break up full name col into fname, lname cols Expand / Collapse
Author
Message
Posted Thursday, November 13, 2003 7:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #86770
Posted Friday, November 14, 2003 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 17, 2004 6:23 AM
Points: 45, Visits: 1
Hey Jonathan,
ThE PARSENAME() solution looks good. I'm validating the results. Thanks everyone for the contributions.





Post #86771
Posted Monday, November 17, 2003 2:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 21, 2014 9:44 AM
Points: 30, Visits: 212
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




Post #86772
Posted Monday, November 17, 2003 3:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
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)



Post #86773
Posted Monday, November 17, 2003 7:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 3, 2005 3:17 PM
Points: 504, 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.
Post #86774
Posted Tuesday, November 18, 2003 5:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, 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
Post #86775
Posted Wednesday, December 3, 2003 10:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:06 PM
Points: 977, Visits: 279
[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)

*/



Post #86776
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse