Hi Guys,
I'm fairly beginner when it comes to sql so reaching out for some help.
I have a column that contains names such as
Betty White
Trump, Donald

I need to create a mnemonic column. The mnemonic must be first 3 letters of last, first 3 letters of first. Such as:
WHIBET
TRUDON

I can do it in multiple steps... but I need to do it in a simple select statment.
Could anyone help me with that?
MUCH appreciated and thanks in advance for your time!

okay, show the multiple steps, and i'll show you how to combine them

Very crude... I apologize in advance.

---Add required fields to create mnemonic---
alter table vendor add firstname varchar (100),lastname varchar (100),mnemonic varchar (6)
--select F31,firstname,lastname,mnemonic from vendor
--select F31,firstname,lastname from vendor where F31 like '%,%'--431

---Seperate name, firstname, lastname ---
--select F31,charindex(',',F31),substring(F31,1,charindex(' ,',F31)),substring(F31,charindex(',',F31)+1,55)fro m vendor where F31 like '%,%'--431charindex(' ,',F31)> 0
update vendor set firstname = substring(F31,charindex(',',F31)+1,55),lastname = substring(F31,1,charindex(',',F31))where F31 like '%,%'
update vendor set lastname =replace(lastname,',',' ')

--select F31,charindex(' ',F31),substring(F31,1,charindex(' ',F31)),
--substring(F31,charindex(' ',F31)+1,55)from vendor where F31 not like '%,%'--431charindex(' ,',F31)> 0
update vendor set firstname = substring(F31,1,charindex(' ',F31)),lastname = substring(F31,charindex(' ',F31)+1,55)where F31 not like '%,%'

---Checked for lastnames which start with MRS/MR/MS---
--select * from vendor where lastname like 'MRS%'
--select * from vendor where lastname like 'MR%'
--select * from vendor where lastname like 'MS%'
update vendor set lastname = 'HUNTER',firstname = 'LINDA' where lastname like 'MS%'

---Remove Spaces from firstname as a result of seperation---
update vendor set firstname = dbo.adapt(firstname)

--select left(lastname,3)+left(firstname,3),lastname,firstn ame from vendor

---Create Mnemonic---
update vendor set mnemonic = left(lastname,3)+left(firstname,3)


----------------------
---dbo.adapt function---
----------------------
USE [Chantale_test]
GO
/****** Object: UserDefinedFunction [dbo].[adapt] Script Date: 04/11/2012 10:55:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[adapt] (@text nvarchar(500)) returns nvarchar(500)
as
begin
declare @result nvarchar(500)
set @text=ltrim(rtrim(isnull(@text,'')))
set @result=''
if @text>''
begin
set @result=@text
declare @i as int, @c as char(1)
set @i=1
while @i<=len(@text)
begin
set @c=substring(@text,@i,1)
if @c in ('å','ä','ã','â','á','à')
set @result=replace(@result,@c,'a')
if @c in ('c','c','c','ç')
set @result=replace(@result,@c,'c')
if @c in ('ë','ê','é','è')
set @result=replace(@result,@c,'e')
if @c in ('ï','î','í','ì')
set @result=replace(@result,@c,'i')
if @c in ('n','ñ')
set @result=replace(@result,@c,'n')
if @c in ('ö','õ','ô','ó','ò')
set @result=replace(@result,@c,'o')
if @c in ('ü','û','ú','ù')
set @result=replace(@result,@c,'u')
if @c='ý'
set @result=replace(@result,@c,'y')
if @c not in ('0','1','2','3','4','5','6','7','8','9','q','w',' e','r','t','y','u','i','o','p','a','s','d','f','g' ,'h','j','k','l','z','x','c','v','b','n','m')
set @result=replace(@result,@c,'')
set @i=@i+1
set @result=upper(@result)
end
end
return @result
end

So your names are stored as single strings, rather than as separate components?
I've attached a function that might help you. Use it to parse out the FirstName and LastName from your strings, and then just use the LEFT() function to get the first three characters.

Hmmm the person I'm helping didn't want to ad a function onto this LIVE db.
If there is any other way in one select statement to do this, please let me know :-) Thanks again!

the sample data you gave had only two formats

now i see that there are actually many different formats

catering for all of them with a series of CASE expressions in a single update statement is still possible, but now it's insanely complicated

tell us again why you can't do a series of updates?

Tell you what, take a look at my function and if you can find a way to encapsulate all that comprehensive logic into a single command, please send it to me when you are done!

There's no reason you can't add a function to a database. The code is open for review.

Thanks for all your help everyone!
My coworker asked me to create him a select statement that will show the new mnemonic. I told him that I didn't think it was (easily) possible. I provided him the alternative that I pasted in and said clean the spreadsheet in a test db, then import it into live and run your select.
He insisted there must be a way... so I just thought I'd check with some experts.
Thanks again for taking a look!
Chantale

If those were covered all cases(i.e. first name and last name only. No others. separated by a blank or comma and a blank", "),
how about this?
(Not tested.)

Thats exactly what I'm looking for!
The error I get when I run it is this:
Msg 402, Level 16, State 1, Line 1
The data types nvarchar and nvarchar are incompatible in the boolean OR operator.
**pls note, I've removed some pipes where they were double. E.g. || changed to |. As it had given me an incorrect syntax error.

no, you will want to use plus signs instead of double pipes, as this is microsoft sql server

poor tonkuma gave you standard sql concatenation operator

It worked!!!
Thank you guys so much. I really do appreciate it.


I wish I could help you in return... but um... hahaa as you can see my skills are pretty basic.

Thanks again.

You'll be fine with that solution until someone enters a name like "Bob Smith Jr." or "Smith, Robert T. III" or "Dr. Robert James Smith, MD".

95% will be fine though, so I'll take it! It was exactly what we were looking for. Great forum!

Yes, but won't that make the 1 percenters angry. Obama Nation doesn't want us to do that