sql server r2 2008 - trim

Question

5.00/5 (1 vote)

See more:

How can I trim the spaces between lastname and email extension?

Goal: I am updating email Address column that is NULL with First and lastname and email extension.

I was able to trim spaces between firstname and last name for the records that is NULL but cannot get rid of the spaces between last name and email extension.

Email Address column is showing as: John.Doe @xxx.net

need to take the spaces out between LastName and email extension to make it to show: John.Doe@xxx.net

My Code:
UPDATE tbltest
SET [Email Address] = LTRIM((RTRIM(([First Name]))+ '.'+[Last Name]+'@xxx.net'))
WHERE [Email Address] IS NULL

Thank you for your time.

Posted 22-Jan-16 7:42am

Member 11829482

Add a Solution

Comments

Member 12001186 22-Jan-16 13:50pm

Since you dont want any spaces why not use REPLACE(var, ' ', '') ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael_Davies · Accepted Answer · 2016-01-22T07:57:00

Solution 1

Update the field using the REPLACE function and replace ' ' with ''.

UPDATE tbltest SET [Email Address] = REPLACE([Email Address],' ','');

Posted 22-Jan-16 7:57am

Michael_Davies

Comments

OriginalGriff 22-Jan-16 14:03pm

Since spaces aren't allowed in email addresses at all, that's the best solution! :thumbsup:

Member 11829482 22-Jan-16 14:50pm

Thanks solution 2 and yours work.

jgakenhe · Accepted Answer · 2016-01-22T08:05:00

Solution 2

SQL

--You can also use LTRIM and RTRIM on the LastName like you did with the FirstName or you can use REPLACE function like Michael showed in solution 1.
UPDATE tbltest
SET [EmailAddress] = LTRIM(RTRIM([FirstName]))+ '.' + LTRIM(RTRIM([LastName])) + '@xxx.net'
WHERE [EmailAddress] IS NULL

Posted 22-Jan-16 8:05am

jgakenhe

Comments

Member 11829482 22-Jan-16 14:50pm

Thanks solution 1 and your work.