Extract name into First and Last from string

karen1974
karen1974 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi karen1974,

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

Author

Commented:
Sure.

Currently names are in format:

Smith,Jane I

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

Thanks.

Commented:
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

Author

Commented:
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?
Commented:
You don't have to set variable for this:

SELECT SUBSTRING(Name, 1, CHARINDEX(',', Name)-1) AS LastName, 
	SUBSTRING(Name, CHARINDEX(',', Name)+1, CHARINDEX(' ', Name)-CHARINDEX(',', Name)-1) AS FirstName,
	SUBSTRING(Name, CHARINDEX(' ', Name)+1, LEN(Name)) AS MiddleIniatial 
FROM table

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial