Want to seprate name from charindex

If you need to treat a name as separate parts, then get them entered as separate parts and store them separately - you can't assume that a user will have just two names( or even more than one!), or that the one to the left of a space is the "first name" - it can be the family name.

In this case, that's almost certainly what is causing your error: if someone in your database only has one name then that is exactly the error you will get, becuase if there is no match, CHARINDEX will return zero ...

You get the specific message you're getting when you don't specify the size of the variable NVARCHAR so THAT defaults to 1. And the LENGTH is, after all your math, therefore zero.

Here's what shows my drift.

DECLARE @exampleY as nvarchar(20)
SET @exampleY = 'Nater Termi'	

SELECT LEFT(@exampleY,CHARINDEX(' ',@exampleY)-1) AS [pisanCP01]-- 'Nater'
SELECT RIGHT(@exampleY,CHARINDEX(' ',@exampleY)-1) AS [pisanCP0A]-- 'Termi'

IF you did this:

DECLARE @exampleZ as nvarchar
SET @exampleZ = 'Scobie Agent'

Then tried to get the length of the string you're inputting, you'd get this:

SELECT LEN(@exampleZ)
/*
     1
*/

Instead of '12'