Aleks
asked on
SQL to separate last name from first name
I have a table called 'cases', one of the fields is a field called 'Name', it includes the full name of the person.
I need to separate the name and move the first name to the "FirstNm" field and the last name into the "LastNm" field
How can I run a query so that it will remove any blank characters (spaces) before the first word, then the first word is the "FirstNm" and everything after the first word (space), will be the last name, the last name may include spaces, such as "De La Praga"
So that this name: Ana De La Praga (Name), would be separated as:
FirstNm: Ana
LastNm: De La Praga
Table Name: cases
Helps is greatly appreciated.
I need to separate the name and move the first name to the "FirstNm" field and the last name into the "LastNm" field
How can I run a query so that it will remove any blank characters (spaces) before the first word, then the first word is the "FirstNm" and everything after the first word (space), will be the last name, the last name may include spaces, such as "De La Praga"
So that this name: Ana De La Praga (Name), would be separated as:
FirstNm: Ana
LastNm: De La Praga
Table Name: cases
Helps is greatly appreciated.
ASKER
Can we please us the code with the table and field names described above? I am not interested in the Middle Name.
The first word as explained is the FirstNm, then a space, anything after that space is LastNm
The first word as explained is the FirstNm, then a space, anything after that space is LastNm
It does use the 3 field names you mentioned: Name, FirstNm and LastNm. If you are using SQL Server, please try the previous example.
,,,,
UPDATE tbl
SET FirstNm = CASE WHEN SpacePos > 1 THEN SUBSTRING(Name, 1, SpacePos) ELSE Name END
, LastNm = CASE WHEN SpacePos > 1 THEN SUBSTRING(Name, SpacePos+1, LEN(Name)-SpacePos) ELSE NULL END
...
,,,,
UPDATE tbl
SET FirstNm = CASE WHEN SpacePos > 1 THEN SUBSTRING(Name, 1, SpacePos) ELSE Name END
, LastNm = CASE WHEN SpacePos > 1 THEN SUBSTRING(Name, SpacePos+1, LEN(Name)-SpacePos) ELSE NULL END
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked great! Thx.
Glad it helped!
Open in new window