[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Create email address from reversed name field

I have a column of data ('csUsername')in my table ('Desktops') which displays a user's name in the format - SURNAME, FIRSTNAME. (surname comma firstname)
I want to insert that data into another field in the table called 'csEmail' in the format FIRSTNAME.SURNAME (firstname dot surname) and then by default have '@uoc.com' put onto the end so that it looks like 'firstname.surname@uoc.com'

How can this be done?
0
UOC
Asked:
UOC
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one out and hope this helps
Haven't tested as I dont have access to SQL now.
SELECT SUBSTRING(rtrim(csUsername), CHARINDEX(',', rtrim(csUsername)), LEN(rtrim(csUsername)) - CHARINDEX(',', rtrim(csUsername))) + '.' +
SUBSTRING(rtrim(csUsername), 1, CHARINDEX(',', rtrim(csUsername))) + '@uoc.com' as Email
FROM Desktops

Open in new window

0
 
ralmadaCommented:
To update the desktops table, try this:
update desktops
set csEmail = ltrim(right(csUserName, charindex(',', reverse(csUserName)) - 1)) + '.' + 
left(csUserName, charindex(',', csUserName) - 1) + '@uoc.com'

Open in new window

0
 
EggpatchCommented:
you want to add a multiple string in one.. try this..

email = "variable_fname" + "."  + "variable_surename" + "@uoc.com"

that result is: variable_fname.variable_surename@uoc.com

example:

email = "juan" + "."  + "dela_cruz" + "@uoc.com"

result: juan.dela_cruz@uoc.com


hope it helps.. ^_^
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
UOCAuthor Commented:
Hi ralmada,

Tried your code but go the error - Invalid length parameter passed to the substring function.
0
 
ralmadaCommented:
Does your csUserName column have null records or records without a comma? Try this to double check:
select * from desktops where csUserName is null or charindex(',', csUserName) = 0
If so, you might want to consider this alternative:

update desktops
set csEmail = case when csUserName is not null or charindex(',', csUserName) <> 0 then 
ltrim(right(csUserName, charindex(',', reverse(csUserName)) - 1)) + '.' + 
left(csUserName, charindex(',', csUserName) - 1) + '@uoc.com' ELSE csEmail END

Open in new window

0
 
UOCAuthor Commented:
There were null records but your revised code still returns the same error.
0
 
ralmadaCommented:
There might not be any nulls, but there are csUserName without commas. So try like this:
 

update desktops
set csEmail = case when charindex(',', csUserName) <> 0 then 
				ltrim(right(csUserName, charindex(',', reverse(csUserName)) - 1)) + '.' + 
				left(csUserName, charindex(',', csUserName) - 1) + '@uoc.com' ELSE csEmail END

Open in new window

0
 
UOCAuthor Commented:
Thanks for your perseverance ralmada, that was what I needed. Excellent!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now