Parse Name Field into First Name Last Name
in [Programming SQL]
|
Prev: Collation conflict when executing stored procedure
Next: Display results horizontal and not vertical
From: Brian Conner via SQLMonster.com on 8 Dec 2009 14:47 I have a field called [Patient Name] that I want to parse out into a First Name Field and Last Name Field. I am using the Charindex() to find the comma that seperates the First and Last Name, I am then passing the Charindex value to the Left Function to grab the Last Name. Below is what the code looks like: LEFT([Patient Name],Charindex(',', [Patient Name])-1) AS LAST_NAME I am getting the below error when using the above code Invalid length parameter passed to the LEFT or SUBSTRING function. Warning: Null value is eliminated by an aggregate or other SET operation. Any suggestions are greatly appreciated -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
From: Michael Coles on 8 Dec 2009 14:52 Your CHARINDEX is returning 0; that is, you have some Patient Names with no comma in them. CASE WHEN CHARINDEX(',', [Patient Name]) > 0 THEN LEFT([Patient Name], CHARINDEX(',', [Patient Name]) - 1) ELSE [Patient Name] END AS LAST_NAME "Brian Conner via SQLMonster.com" <u47161(a)uwe> wrote in message news:a04b978a42f95(a)uwe... >I have a field called [Patient Name] that I want to parse out into a First > Name Field and Last Name Field. I am using the Charindex() to find the > comma > that seperates the First and Last Name, I am then passing the Charindex > value > to the Left Function to grab the Last Name. Below is what the code looks > like: > > > LEFT([Patient Name],Charindex(',', [Patient Name])-1) AS LAST_NAME > > > > I am getting the below error when using the above code > > Invalid length parameter passed to the LEFT or SUBSTRING function. > Warning: Null value is eliminated by an aggregate or other SET operation. > > Any suggestions are greatly appreciated > > -- > Brian Conner > > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1 >
From: Brian Conner via SQLMonster.com on 8 Dec 2009 16:24
Thank you very much for your help, it worked.... Michael Coles wrote: >Your CHARINDEX is returning 0; that is, you have some Patient Names with no >comma in them. > >CASE WHEN CHARINDEX(',', [Patient Name]) > 0 > THEN LEFT([Patient Name], CHARINDEX(',', [Patient Name]) - 1) > ELSE [Patient Name] > END AS LAST_NAME > >>I have a field called [Patient Name] that I want to parse out into a First >> Name Field and Last Name Field. I am using the Charindex() to find the >[quoted text clipped - 12 lines] >> >> Any suggestions are greatly appreciated -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1 |