March 14, 2005 at 9:05 am
Looking for some help
String 'Smith, John M' Note Space after ,
Looking to take a string
Last, Fisrt M and convert it into
First M Last
Ex: 'John M Smith'
Thanks in Advance
March 14, 2005 at 9:13 am
I think you're better off making 3 columns : first, last and middle name and split that column into those 3, then you can join those however you want in the stored procs or even better in the application. It think you should look up the functions pathindex and substring in the books online.
March 14, 2005 at 9:15 am
This kind of t-sql will work if the string is always this well formed. But will probably need extending using regexp if there are apostrophes in names.
declare @Name varchar(50)
declare @newName varchar(50)
declare @Comma int
set @Name='Smith, John M'
set @Comma=charIndex(',',@Name)
set @newName=substring(@Name, @Comma+2, len(@Name)) + ' ' + substring(@Name,1,@Comma-1)
select @Name as original_name, @newName as new_name
March 14, 2005 at 12:12 pm
Thanks that worked Great...
I real time save thanks a million.
March 15, 2005 at 6:18 pm
ignore the ATOM rules at your own risk...they are not arbitrary and were created for good reason.
-----------------------------------------------------------------------------------------
if object_ID('tempdb..#Person') is not null drop table #Person
create table #Person
(
PersonID int identity not null,
FirstName varchar(255),
MiddleName varchar(255),
LastName varchar(255)
)
Insert #Person values ('Calvin', 'Daniel', 'Lawson')
select
FirstName + ' ' + cast(MiddleName as varchar(1)) + ' ' + LastName as [Name],
LastName + ', ' + FirstName + ' ' + MiddleName as LastNameFirst
from #Person
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply