Pulling first 4 digits/then first after comma

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

General SQL Server Forums

New to SQL Server Programming

Pulling first 4 digits/then first after comma

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-10-25 : 12:50:52

I have a field that contains a name of staff, like last, first. If I wanted to pull the first 4 letters on the last name followed by the first letter of the first name, is there a way to pull the first letter after a comma and space? This is how I am currently pulling the info:

left(isnull(replace(s.name,',',''),'') + space(5),5

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 12:57:09

One way to do this:

DECLARE @name VARCHAR(32) = 'Jones, Mary';
SELECT 
   LEFT(@name,4) AS Lastname4, 
   LEFT( LTRIM(STUFF(@name,1,CHARINDEX(',',@name),'')),1) FirstInitial;

jpost
Starting Member

43 Posts

Posted - 2012-10-25 : 14:06:00

Can you use the declare in the middle of a select statement or do I need to do a sub select?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 14:38:07

I was only showing an example. You don't need to declare an intermediate variable; you can use it directly against the column from your table. Based on your initial posting where you indicated the column to be s.name, the query would be likethis:

SELECT 
   LEFT(s.name,4) AS Lastname4, 
   LEFT( LTRIM(STUFF(s.name,1,CHARINDEX(',',s.name),'')),1) FirstInitial
FROM
   ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources