I am using Sql Server. I am trying to write an sql query to divide the column named fullName into firstName and lastName columns. but I keep on getting this error:

  Msg 537, Level 16, State 2, Line 1
  Invalid length parameter passed to the LEFT or SUBSTRING function.

This is my query:

 SELECT  CASE
      WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', ''))   1) > 0
      THEN
         Substring (FullName, 1, Charindex (' ', FullName) - 1)
      ELSE
         ''
   END AS FirstName,      
   CASE
      WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', ''))   1) > 0
       THEN REVERSE(SUBSTRING(REVERSE(FullName),
                   1,
                   CHARINDEX(' ', REVERSE(FullName)) - 1))
       ELSE FullName
   END AS LastName,email, c.customerid
FROM            Customer c

What can be causing this error ? I have verified that there are no fullname records that are empty '' or null. But fullname could hold just a firstname if someone forgot to add their lastname - I am not sure if this could be the issue ?

If I remove the 1:

 WHEN (LEN (FullName) - LEN (REPLACE (FullName, ' ', ''))) > 0 

Then it returns results - but the firstname and lastname columns are both empty even though fullname actually contains the first name but only the first name - for instance 'Seyhan'. How can I fix it so that at least it returns the firstname ?

CodePudding user response：

Error message itself is self-explanatory and it's clearly saying that you have specified an invalid length parameter to the SUBSTRING function.

SELECT SUBSTRING('SQL Tutorial', 1, LEN(SUBSTRING('SQL Tutorial',1,(CHARINDEX(' ', 'SQL Tutorial'))))) AS ExtractString;

This above query returns First part “SQL”

https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_substring

Try using STRING_SPLIT ( string , separator [ , enable_ordinal ] )

SELECT value FROM STRING_SPLIT ('Lorem ipsum dolor sit amet.', ' ');

In a practice run, the preceding SELECT returned following result table:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16