February 3, 2015 at 3:26 pm
So the User_Manager table has a field called Username it is a first and last name in 1 field. I want to break them apart.
So I am using the following query. On the first left I had to add the +' ' at the end or I was getting a invalid argument error on the left which makes no sense as all records returned a number so the minus 1 never put it into a negative number. to test this I ran the it as a where and said show me everything less then 0 and it returned no results
Now on the right I am getting an invalid argument again. The third line is just the parameter and it returns a value like it should as a test. So why will it not work in conjunction with the right?
Also the table has no null records
Select
LEFT(UM.[User_Name], CHARINDEX(' ',UM.[User_Name]+ ' ')-1) as Firstname
,Right(UM.[User_Name], LEN(UM.User_Name) - CHARINDEX(' ',UM.[User_Name]))
,LEN(UM.User_Name)- CHARINDEX(' ',UM.User_Name) as lastnameCount
From tblCustomers
Inner join User_Manager as UM on tblCustomers.Sales_Person_ID = UM.User_Id
February 3, 2015 at 4:17 pm
I don't get any error, I'm not sure if I was supposed to get one.
Here's my test along with alternative methods.
WITH SampleData AS(
SELECT 'Luis Cazares' AS [User_Name] UNION ALL
SELECT 'NoSpace' UNION ALL
SELECT 'Two Spaces here' UNION ALL
SELECT 'Three Spaces over here' UNION ALL
SELECT '' UNION ALL
SELECT NULL
)
Select
LEFT(UM.[User_Name], CHARINDEX(' ',UM.[User_Name]+ ' ')-1) as Firstname
,Right(UM.[User_Name], LEN(UM.User_Name) - CHARINDEX(' ',UM.[User_Name])) AS LastName
,LEN(UM.User_Name)- CHARINDEX(' ',UM.User_Name) as lastnameCount
,SUBSTRING(UM.[User_Name], CHARINDEX(' ',UM.[User_Name] + ' ') + 1, 8000) AS LastNameAlternative
,ISNULL( NULLIF( LEN(UM.User_Name) - CHARINDEX(' ',UM.User_Name + ' '), -1), 0) as lastnameAlternativeCount
From SampleData UM
February 3, 2015 at 4:23 pm
So using your first two lines which are the same as mine I am getting a Invalid length parameter passed to the right function.
February 3, 2015 at 5:03 pm
DaveK2014 (2/3/2015)
So using your first two lines which are the same as mine I am getting a Invalid length parameter passed to the right function.
After adding another possibility, I found that the error will appear when you only have trailing spaces which will give you a negative value for second parameter in RIGHT().
Using my alternative or RTRIM() would solve the problem.
WITH SampleData AS(
SELECT 'Luis Cazares ' AS [User_Name] UNION ALL
SELECT 'NoSpace' UNION ALL
SELECT 'TrailingSpace ' UNION ALL
SELECT ' LeadingSpace' UNION ALL
SELECT 'Two Spaces here' UNION ALL
SELECT 'Three Spaces over here' UNION ALL
SELECT '' UNION ALL
SELECT NULL
)
Select
LEFT(UM.[User_Name], CHARINDEX(' ',UM.[User_Name]+ ' ')-1) as Firstname
,Right(UM.[User_Name], LEN(UM.User_Name) - CHARINDEX(' ',RTRIM( UM.[User_Name]))) AS LastName
,LEN(UM.User_Name)- CHARINDEX(' ',UM.User_Name) as lastnameCount
,SUBSTRING(UM.[User_Name], CHARINDEX(' ',UM.[User_Name] + ' ') + 1, 8000) AS LastNameAlternative
,ISNULL( NULLIF( LEN(UM.User_Name) - CHARINDEX(' ',UM.User_Name + ' '), -1), 0) as lastnameAlternativeCount
From SampleData UM
February 4, 2015 at 7:33 am
sorry I am not 100% sure I follow. Why would the method that I had not work? If you do the Len and char index line it does return the right number to calculate the last name but the right formula will not take that the parameter.
February 4, 2015 at 10:50 am
Because you'll have problems with trailing spaces. Have you used the code that I posted to see the different cases and their results?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply