karen1974
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,CHARI NDEX('(',C olumnName) +1,CHARIND EX(')',Col umnName)-( CHARINDEX( '(',Column Name+1))
+ ' '+
SUBSTRING(ColumnName', 1, CHARINDEX(',',ColumnName)- 1)
FROM table
3. SELECT
substring(Name, charindex('(', Name), charindex(')', Name) - charindex('(', Name)) as Name FROM table
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(','
substring(@a,0,CHARINDEX('
FROM table
2. SELECT SUBSTRING(ColumnName,CHARI
+ ' '+
SUBSTRING(ColumnName', 1, CHARINDEX(',',ColumnName)-
FROM table
3. SELECT
substring(Name, charindex('(', Name), charindex(')', Name) - charindex('(', Name)) as Name FROM table
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.
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you post some some values from Name column as examples?