Avatar of karen1974
karen1974
Flag for United States of America asked on

Extract name into First and Last from string

Hello,

I've looked this up in the knowledge database and it *seemed* to make sense but just isn't happenening correctly so thanks in advance for assistance.

Issue is there is a FirstName, LastName MI string that I'm trying to extract from a file in order to verify memberIDs.

The column name is simply "Name" and is NVARCHAR(250).

I've used the below -- doesn't matter to me if it's declared or just part of a SELECT though would be good to reuse -- but I'm not correctly doing so as is -- the result is giving me 1000s of NULL row values irrespective of what used.

Have tried:

1 declare @a nvarchar(250)
set @a='Name'

select substring(@a,CHARINDEX(',', @a)+1,CHARINDEX(' ', @a,CHARINDEX(',', @a)+2)-CHARINDEX(',', @a)) + ' ' +
substring(@a,0,CHARINDEX(',', @a)) as UpName
FROM table
 
2. SELECT SUBSTRING(ColumnName,CHARINDEX('(',ColumnName)+1,CHARINDEX(')',ColumnName)-(CHARINDEX('(',ColumnName+1))
+ ' '+
SUBSTRING(ColumnName', 1, CHARINDEX(',',ColumnName)-1)
FROM table

3.  SELECT
substring(Name, charindex('(', Name), charindex(')', Name) - charindex('(', Name)) as Name FROM table
SQL

Avatar of undefined
Last Comment
Rimvis

8/22/2022 - Mon
Rimvis

Hi karen1974,

Could you post some some values from Name column as examples?
karen1974

ASKER
Sure.

Currently names are in format:

Smith,Jane I

Would like to extract into 3 columns for Last name, First name, Middle Initial.

Thanks.
Rimvis

Is this what you want?

declare @a nvarchar(250)
set @a='Smith,Jane I'


SELECT SUBSTRING(@a, 1, CHARINDEX(',', @a)-1) AS LastName, 
	SUBSTRING(@a, CHARINDEX(',', @a)+1, CHARINDEX(' ', @a)-CHARINDEX(',', @a)-1) AS FirstName,
	SUBSTRING(@a, CHARINDEX(' ', @a)+1, LEN(@a)) AS MiddleIniatial

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
karen1974

ASKER
Yes -- this is what I'd like to do except I've never done a SET statement where I've had to "dynamically" pass values from a table -- it's always been a static date or name.

So, in the example, there are 3000+ different names from table where column is 'Name'

When I tried doing:
declare @a nvarchar(250)
set @a='Name'

and followed up by doing a SELECT FROM the table, I'm getting blank rows.
Does this make sense?
ASKER CERTIFIED SOLUTION
Rimvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question