String function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

String function

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2005-05-16 : 12:20:26

I forget how to do this:

How do I extract the last name from the following string?

Waugh,Matthew N

I thought there was some index function...but I can't remember. While you're at it...how would I get the last name, or the initial for that matter?

Thanks in advance.

--------------------------------

I know enough to know that I don't know enough.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-16 : 12:31:46

[code]CREATE FUNCTION dbo.LFM_Last (@LFM varchar (100))
-- Assumes name format is Last, First M. (period is optional)
RETURNS varchar (100)
AS
BEGIN
SET @LFM = RTRIM(LTRIM(@LFM)) -- Remove left / right blanks
DECLARE @ichr INT
SET @ichr = charindex(',', @LFM) -- Find comma delimiter
IF @ichr = 0
SET @ichr = charindex(' ', @LFM+' ') -- If no comma, find blank delimiter
SET @LFM = LEFT(@LFM, @ichr-1) -- We have what we believe to be the last name
-- Fix names like O Malley => O'Malley
IF LEN(@LFM) > 2 AND SUBSTRING(@LFM, 2, 1) = ' ' AND SUBSTRING(@LFM, 3, 1) <> ' ' BEGIN -- May be missing an apostrophe
IF LEFT(@LFM,1) = 'D' OR LEFT(@LFM,1) = 'O' -- Like D'Almata or O'Riley
SET @LFM = LEFT(@LFM,1) + '''' + SUBSTRING(@LFM,3,100)
END
SET @LFM = REPLACE(@LFM, ' ', ' ') -- ELIMINATE DOUBLE SPACES LIKE LNAME JR
RETURN (@LFM)
END
GO
GRANT EXECUTE ON dbo.LFM_Last TO Public
GO
[/code]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-16 : 12:52:16

That works Sam. However, i make my clients pass them in seperately, and not as a single string

*need more coffee*

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources