November 18, 2003 at 5:45 am
quote:
You don't say anything about the quality ofI 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
December 3, 2003 at 10:48 am
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)
*/
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply