Selecting parts of a string
-
Hi, I need some help with a function - well 3 actually - to pull out the require parts of a string. I have a field called strFiDesc_FD that contains customer names in the format "LastName FirstName Title" all separated by a space. I can work out how to get the LastName and FirstName, but am struggling with the Title. Here's what i have so far, any comments will be welcome to improve the current structure of the functions, but what i am really interested in is getting the Title part of the string out into a third function.
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'fn_TicketPaxLastName' )
BEGIN
DROP FUNCTION dbo.fn_TicketPaxLastName
END
GO
CREATE FUNCTION dbo.fn_TicketPaxLastName ( @strBBranchCode CHAR ( 2 ), @lFFoldNo INT, @nFiFoldItemID SMALLINT, @strFiType CHAR( 3 ))
RETURNS VARCHAR ( 10 )
BEGIN
RETURN(
CASE WHEN ( @strFiType = 'TKT' ) THEN
( SELECT SUBSTRING( strFiDesc_FD, 1, CHARINDEX( ' ', strFiDesc_FD )) FROM dbo.FoldItems_TB AS FoldItems_TB
WHERE FoldItems_TB.strBBranchCode_FD = @strBBranchCode
AND FoldItems_TB.lFFoldNo_FD = @lFFoldNo
AND FoldItems_TB.nFiFoldItemID_FD = @nFiFoldItemID )
ELSE
''
END
)
END
GO
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE name = 'fn_TicketPaxFirstName' )
BEGIN
DROP FUNCTION dbo.fn_TicketPaxFirstName
END
GO
CREATE FUNCTION dbo.fn_TicketPaxFirstName ( @strBBranchCode CHAR ( 2 ), @lFFoldNo INT, @nFiFoldItemID SMALLINT, @strFiType CHAR( 3 ))
RETURNS VARCHAR ( 10 )
BEGIN
RETURN(
CASE WHEN ( @strFiType = 'TKT' ) THEN
( SELECT SUBSTRING( strFiDesc_FD, CHARINDEX( ' ', strFiDesc_FD ),CHARINDEX( ' ', strFiDesc_FD )) FROM dbo.FoldItems_TB AS FoldItems_TB
WHERE FoldItems_TB.strBBranchCode_FD = @strBBranchCode
AND FoldItems_TB.lFFoldNo_FD = @lFFoldNo
AND FoldItems_TB.nFiFoldItemID_FD = @nFiFoldItemID )
ELSE
''
END
)
END
Thanks!
-
You don't need to build a function for this... if you are always guaranteed to have 3 parts, then there's a function built into SQL Server... ya just gotta give it what it wants...
DECLARE @String VARCHAR(30)
SET @String = 'LastName FirstName Title'
SELECT
PARSENAME(REPLACE(@String,' ','.'),3), -- Returns the LastName
PARSENAME(REPLACE(@String,' ','.'),2), -- Returns the FirstName
PARSENAME(REPLACE(@String,' ','.'),1) -- Returns the title
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I'm probably pointing out something you guys already realize, but the original poster seems to be making the assumption that there are no spaces in names or titles, and the nice solution by Jeff is making the assumption that there are no periods in names or titles. Both assumptions are fine if true, of course
-
Also, Jeff's solution as is requires there be exactly one space between each part.
This won't be safe if humans are typing these in, but then again, if humans are typing these in, the whole field definition is probably unsafe, and there is probably no safe solution
-
Thanks for the pasename suggestion - this will work most of the time, but on occassion the name might be just "LastName FirstName" or "LastName Title" unfortunately!
Any ideas/suggestions with the function???
Thanks!
-
I'm with Jeff, this doesn't need a function(s), although I'd attack it a bit differently.
CHARINDEX finds your first space and gives you Firstname, REVERSE & CHARINDEX will give you the last space and Title, a combination of both of these, SUBSTRING and some trimming will give you the lastname.
This is probably a best as you'll get.
Who wants to make a guess how the name "Krishna Gopala Venkata Ganugapati Mr" fits into this logic
--------------------
Colt 45 - the original point and click interface
-
I knew it! I've always hated composite name columns... I'm trying to get them to change it but we have a 3rd party app at work where they allow up to 128 bytes for a full name and you can type anything. How's 'bout "Sandy J. Hale and Julie Lynne Smith Jones" for a live in partnership?
James... the ParseName solution will only work for "LastName FirstName Title" and, like some of the other good folks have pointed out, works only if you have the correct spacing and no periods in the name. Intelligent full name parsing is quite the pain... you need to identify all the rules and also make the realization that you might not be able to get SQL (or any app, for that matter) to do it all.
So, far, you've defined"
"LastName FirstName Title"
...and you'd like to be able to do
"LastName FirstName"
I gotta ask...
What do you want to do with last names that have two words?
What do you want to do with extra spaces? Do they mean something (eg. Title missing) or should they just go to the bit bucket?
Are there other "anomolies" that you want to look for such as "LastName Title", etc.
What are ALL the rules for this column?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
The name string is read in from a third party app. into my database. Most times there will be a last, first name and title, but sometimes the name might belong to a child and will therefore not have a title. These parts will always be separatred by a single space and a last name will have to be hyphenated if it is a double-barrell name.
I am trying to select the name parts into a 3 separate functions so that our clients can manipulate the name parts in various SQL views.
This operation needs to be compatible in both SQL Server and Sybase - dont think Sybase supports REVERSE as suggested by Phill.
I realise that my function will not be bullet-proof but all i need ot worry about is getting the name parts out - it is not my concern if the names are not correctly entered in the third party app.
Thanks for your help guys!
-
Hi all,
I started playing with the PARSENAME way of doing this (for fun, and taking into consideration what's been mentioned), and got a bit carried away.
I'm not certain it will help you, but I enjoyed creating it...
The first bit is with sample data and no functions, and the second bit is just showing the start of how you could adapt the first bit into a function or two.
--This whole SQL script is safe to run
--Create a table of sample data
DECLARE @SampleData TABLE (Id INT, Name VARCHAR(100), Description VARCHAR(50), TidiedName VARCHAR(100))
INSERT INTO @SampleData (Id, Name, Description)
SELECT 1, 'Smith John Mr' , 'LastName FirstName Title'
UNION SELECT 2, 'Smith John' , 'LastName FirstName'
UNION SELECT 3, 'Smith Mr' , 'LastName Title'
UNION SELECT 4, 'Smith-Jones John Mr' , 'Double-barrelled LastName'
UNION SELECT 5, 'Smith John Mr' , 'Crazy spacing'
UNION SELECT 6, 'Smith. John Mr..' , 'Crazy full stops'
UNION SELECT 7, ' Smith John Mr' , 'Leading space'
UNION SELECT 8, 'Smith John Mr ' , 'Trailing space'
UNION SELECT 9, 'Smith John HRH' , 'Unrecognised title'
--Get rid of extra dots and spaces
UPDATE @SampleData SET TidiedName = Name
UPDATE @SampleData SET TidiedName = REPLACE(TidiedName, '.', '')
WHILE EXISTS(SELECT * FROM @SampleData WHERE NOT PATINDEX('% %' , TidiedName) = 0)
UPDATE @SampleData SET TidiedName = REPLACE(TidiedName, ' ', ' ')
UPDATE @SampleData SET TidiedName = RTRIM(LTRIM(TidiedName))
--Create a table of recognised titles
DECLARE @Titles TABLE (Title VARCHAR(10))
INSERT INTO @Titles VALUES ('Mr')
INSERT INTO @Titles VALUES ('Mrs')
INSERT INTO @Titles VALUES ('Ms')
INSERT INTO @Titles VALUES ('Miss')
INSERT INTO @Titles VALUES ('Dr')
--Get LastName, FirstName and Title
SELECT
Id,
Name,
Description,
TidiedName,
PARSENAME(REPLACE(TidiedName,' ','.'), LEN(TidiedName) - LEN(REPLACE(TidiedName, ' ', '')) + 1) AS LastName,
CASE WHEN b.Title IS NULL THEN PARSENAME(REPLACE(TidiedName,' ','.'), LEN(TidiedName) - LEN(REPLACE(TidiedName, ' ', ''))) ELSE NULL END AS FirstName,
CASE WHEN b.Title IS NULL THEN PARSENAME(REPLACE(TidiedName,' ','.'), LEN(TidiedName) - LEN(REPLACE(TidiedName, ' ', '')) - 1) ELSE b.Title END AS Title
FROM
@SampleData a
LEFT OUTER JOIN @Titles b ON PARSENAME(REPLACE(TidiedName,' ','.'), LEN(TidiedName) - LEN(REPLACE(TidiedName, ' ', ''))) = b.Title
GO
--------------------------------------------------
DECLARE @Name VARCHAR(100)
SET @Name = 'Smith John Mr' --LastName FirstName Title
SET @Name = 'Smith John' --LastName FirstName
SET @Name = 'Smith Mr' --LastName Title
--Get rid of extra dots and spaces
DECLARE @TidiedName VARCHAR(100)
SET @TidiedName = @Name
SET @TidiedName = REPLACE(@TidiedName, '.', '')
WHILE NOT PATINDEX('% %' , @TidiedName) = 0
SET @TidiedName = REPLACE(@TidiedName, ' ', ' ')
SET @TidiedName = RTRIM(LTRIM(@TidiedName))
--Create a table of recognised titles
DECLARE @Titles TABLE (Title VARCHAR(10))
INSERT INTO @Titles VALUES ('Mr')
INSERT INTO @Titles VALUES ('Mrs')
INSERT INTO @Titles VALUES ('Ms')
INSERT INTO @Titles VALUES ('Miss')
INSERT INTO @Titles VALUES ('Dr')
--Get Title
IF EXISTS(SELECT * FROM @Titles WHERE Title = PARSENAME(REPLACE(@TidiedName,' ','.'), LEN(@TidiedName) - LEN(REPLACE(@TidiedName, ' ', ''))))
SELECT PARSENAME(REPLACE(@TidiedName,' ','.'), LEN(@TidiedName) - LEN(REPLACE(@TidiedName, ' ', ''))) AS Title
ELSE
SELECT PARSENAME(REPLACE(@TidiedName,' ','.'), LEN(@TidiedName) - LEN(REPLACE(@TidiedName, ' ', '')) - 1) AS Title
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
This is probably not relevant, but there are a lot of other titles in the world, and there are suffixes (such as Jr), and there are multiple surnames which are not normally hyphenated, and patronyms, and infixes/connectors (eg, "de la", or "ibn", or "van"), ...
-
He did say that is was for fun
and it does work for what James posted as a very limited original format 
Ryan, it's nice to see someone besides me have so much fun at this... nice job.
James,
Provided that you meant what you said about the column having such a very limited format and no out of place spaces, the will also work using ParseName... hope Sybase has that...
--===== Create and populate a small test table variable
DECLARE @FullName TABLE (FullName VARCHAR(50))
INSERT INTO @FullName (FullName)
SELECT 'Smith John Mr' UNION ALL
SELECT 'Smith John'
SELECT
CASE
WHEN PARSENAME(REPLACE(FullName,' ','.'),3) IS NULL
THEN PARSENAME(REPLACE(FullName,' ','.'),2)
ELSE PARSENAME(REPLACE(FullName,' ','.'),3)
END AS LastName,
CASE
WHEN PARSENAME(REPLACE(FullName,' ','.'),3) IS NULL
THEN PARSENAME(REPLACE(FullName,' ','.'),1)
ELSE PARSENAME(REPLACE(FullName,' ','.'),2)
END AS FirstName,
CASE
WHEN PARSENAME(REPLACE(FullName,' ','.'),3) IS NULL
THEN NULL
ELSE PARSENAME(REPLACE(FullName,' ','.'),1)
END AS Title
FROM @FullName
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks guys - i went with the simplified PARSENAME inbuilt function in the end!
-
Thanks for the feedback, James.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Well, if we are playing
thenSELECT
REVERSE(ISNULL(PARSENAME(REVERSE(REPLACE(FullName,' ','.')),1),'')),
REVERSE(ISNULL(PARSENAME(REVERSE(REPLACE(FullName,' ','.')),2),'')),
REVERSE(ISNULL(PARSENAME(REVERSE(REPLACE(FullName,' ','.')),3),''))
FROM @FullName
Far away is close at hand in the images of elsewhere.
Anon. -
Something weird happened with last post, so I repost:
Jeff,
it works fine up to the part when 'double barreled' names comes up.
select '''Smith Johnsson'' John Mr'
select '''Smythe Johnsson'' John'
We would need some additional checking for single- or double quotes, since this throws the 'number of spaces' logic in order to figure out where the name/title parts are, and possibly also some 'stripping' to get rid of said quotes from the first and lastname parts.
I too like PARSENAME, it's simple and compact style yields code 'friendly' to the eye, and it's more versatile in it's use than one may first think.
/Kenneth
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply