How to Search Name string for space and convert lastname substring

Question

How to Search Name string for space and convert lastname substring

rons-605185

SSCommitted

Points: 1618
More actions
August 17, 2009 at 1:04 pm

#203852

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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?

GSquared SSC Guru Points: 260824 More actions · Answer 2

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

rons-605185 SSCommitted Points: 1618 More actions · Answer 3

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.

Lowell SSC Guru Points: 323504 More actions · Answer 4

here's something to help visualize it:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rons-605185 SSCommitted Points: 1618 More actions · Answer 5

Lowell-

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

Thanks again.

Lowell SSC Guru Points: 323504 More actions · Answer 6

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!