Problems with RTRIM when creating a field from other fields in SQL

[egstatus]

Hi all,
I'm trying to create an email address field by using the first letter of a Full_Name, then the Last Name from the Full Name, then add the '@' + CompanyName + '.com'.

Here is data I get back after running the query:


CORR_EMPRESA NOMBRE_EMPRESA CORR_EMPLEADO NOMBRE EMPLEADO EMAIL ADDRESS
1 E-COFFEETECH S.A. DE… 1 JUANA MARROQUIN jmarroquin @e-coffeetech.c…
1 E-COFFEETECH S.A. DE… 2 MARTIN MENDEZ mmendez @e-coffeetech.com
1 E-COFFEETECH S.A. DE… 3 SERGIO PEREZ sperez @e-coffeetech.com
1 E-COFFEETECH S.A. DE… 4 ALBERTO RODRIGU… arodriguez @e-coffeetech.c…
1 E-COFFEETECH S.A. DE… 5 JUAN CASTANEDA jcastaneda @e-coffeetech.c…
1 E-COFFEETECH S.A. DE… 6 ALFREDO ASCENCIO aascencio @e-coffeetech.co…
1 E-COFFEETECH S.A. DE… 7 JOSELYN SANCHEZ jsanchez @e-coffeetech.com
1 E-COFFEETECH S.A. DE… 8 RICARDO GUTIERR… rgutierrez @e-coffeetech.com
1 E-COFFEETECH S.A. DE… 9 ANDRES PEREIRA apereira @e-coffeetech.com
1 E-COFFEETECH S.A. DE… 10 MARIA GARCIA mgarcia @e-coffeetech.com

Here is my code:

SELECT A.CORR_EMPRESA, B.NOMBRE_EMPRESA, A.CORR_EMPLEADO, A.NOMBRE_EMPLEADO, 
	LEFT(LOWER(NOMBRE_EMPLEADO),1) + 
	LOWER(RTRIM(SUBSTRING(NOMBRE_EMPLEADO, CHARINDEX(' ',NOMBRE_EMPLEADO)+1,LEN(NOMBRE_EMPLEADO))))
		+'@' + LOWER(B.NOMBRE_COMERCIAL) + '.com' AS EMAIL_ADDRESS
FROM GEN_EMPLEADO AS A
INNER JOIN GEN_EMPRESA AS B ON A.CORR_EMPRESA = B.CORR_EMPRESA 
WHERE ESTADO_ACTIVO = 1

The problem I have is I keep getting few spaces after the last name and before the @ sign of the email address. I have tried both RTRIM and LTRIM and no difference on the result set.

What Am I doing wrong?

Thanks in advance

EG

 

[gmmastros]

Rtrim will only remove trailing spaces. There may be other non printable characters there, like a carriage return line feed or tab.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[egstatus]

Thanks for the tips George, that was exactly the problem, some of the names had a tab at the end others CRLF.