How to Search Name string for space and convert lastname substring

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » How to Search Name string for space and...

How to Search Name string for space and convert lastname substring

Rate Topic

Display Mode

Topic Options

Author

Message

rons-605185

Posted Monday, August 17, 2009 1:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 08, 2009 9:00 AM
Points: 44, Visits: 162

Have Username field in database with data like "Charles Smith". Trying to create part of a trigger that would find the last name and convert the last name into uppercase string. Final results would be 2 fields: Partner_ID and Username where data would be like:

Partner_ID Username

SMITH Charles Smith

I know enough on the Upper function but how do I search the Username string for a space and then pull the lastname string -> convert to UpperCase and then copy the uppercase lastname into the Partner_ID field?

Post #772191

Matt Miller (#4)

Posted Monday, August 17, 2009 1:11 PM

SSCertifiable

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:56 PM
Points: 6,997, Visits: 13,941

You want to read up on the PATINDEX function, which may allow you to find the space you need. I use the word "MAY" because trying to break up names based on some pattern often ends up with bad results, since there are no set rule about last names being single "words".

For example: from Highlander:

Juan Sánchez Villa-Lobos Ramírez

Break that one up correctly......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #772198

GSquared

Posted Monday, August 17, 2009 1:20 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

This will do what you're asking:

declare @Name varchar(100);

select @Name = 'Charles Smith';

select reverse(upper(left(reverse(@Name), charindex(' ', reverse(@Name)))));

As mentioned, this won't do what you need, because it'll fail the first time someone has a name that ends with "II" or "Jr", or "PhD". But as an academic exercise in string manipulation, it'll do what you're asking.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #772204

rons-605185

Posted Tuesday, August 18, 2009 7:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 08, 2009 9:00 AM
Points: 44, Visits: 162

G-Squared:

Your script works and pretty much does what I need. Can you explain exactly what this script is actually doing?
Sorry but still learning the basics of SQL and not entirely familar with the Reverse and Charindex commands/functions?
Thanks.

Post #772684

Lowell

Posted Tuesday, August 18, 2009 7:55 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 11,607, Visits: 27,653

here's something to help visualize it:

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #772723

rons-605185

Posted Tuesday, August 18, 2009 8:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 08, 2009 9:00 AM
Points: 44, Visits: 162

Lowell-

Thanks! This is exactly what I needed and now can understand the full logic and script.

Thanks again.

Post #772763

Lowell

Posted Tuesday, August 18, 2009 8:27 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 11,607, Visits: 27,653

my pleasure; something like this is much easier to understand visually like that. glad i could help.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #772771

« Prev Topic | Next Topic »

Permissions