Link to home
Start Free TrialLog in
Avatar of karen1974
karen1974Flag 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
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Hi karen1974,

Could you post some some values from Name column as examples?
Avatar of 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.
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

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
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial