Solved

# Show only First letter of first name and full last Name

Posted on 2011-10-04
504 Views
Hi Experts,
How do I get the first letter of first name and full last name in query?
example,
Jane.Doe
I want to get is only JDoe.

I only get to is the first letter is
Name: Left([UserName],1) this gave me the first letter of first name.

Thanks
0
Question by:jodstrr2

LVL 16

Expert Comment

Try this:

``````LEFT([UserName],1) + SUBSTRING([UserName], CHARINDEX('.', [UserName])+1, 999)
``````
0

LVL 14

Expert Comment

Hi,

If the format of the name is Jan.Doe you need to put 2 parts together.

First initial of First Name = Left([Name],1)

Last Name is a bit more tedious.  You need to find the position of the period and get everything after that.
Instr([Name],".") will return the position of the "."
Len([Name]) will return the length of the string.
Last Name then can be computed by Right(Instr([Name],".")+1,Len([Name])-Instr([Name],"."))
Combine the 2 expressions:

Name Like You want =  Left([Name],1) & Right(Instr([Name],".")+1,Len([Name])-Instr([Name],"."))

Regards,

Bill
0

LVL 75

Expert Comment

Example:

Left("Jon Dough",1) & Mid("Jon Dough",  InStrRev("Jon Dough"," ")+1)
Returns
JDough
0

LVL 14

Expert Comment

S/B

Bill
0

LVL 75

Expert Comment

So ... generic:

Left(YourName,1) & Mid(YourName,  InStrRev(YourName," ")+1)
JDough
0

LVL 75

Expert Comment

SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName]," ")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));
0

Author Comment

I tried
1). sjklein42's soultion, I got the error message "Undefined function "Substring" in expression" and undefined function "CHARINDEX" in expression"

2).BillDenver's, I got the result is first Letter of first name and numbers.  for example, the result is A7, A10 etc.

3). DatabaseMX's, I got the New name as Jane.Doe
0

LVL 75

Accepted Solution

Is there always a period?

If so then:

Left("Jon.Dough",1) & Mid("Jon.Dough",  InStrRev("Jon.Dough",".")+1)
returns
JDough
0

LVL 75

Expert Comment

SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName],".")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));
0

Author Closing Comment

Works great, thanks!!!!
0

LVL 75

Expert Comment

Sorry for the confusion.  I swear ... I saw a Space instead of a period :-)

mx
0