SQL Help

[the325is]

Hello,

The email format for my company has changed and I am looking for assistance in updating the Sybase Database. We were using first initial last name but now it is first name and last name @bla..bla.com

The table where the email addresses reside is called Contact but the user information is another table called User.

I would like to know if I can update the Contact table with reference to the user table.

The current syntax that I have is:

update contact
set email_address = '(I need to reference the user table here and retain the last portion of the email address @bla.bla.com cause it hasn't changed.)

Thank you in advance,
Wali

[rmiao]

Can you post same sample data?

[Rawhide]

Hopefully, you have an ID field linking the user table to the contact table. I would expect the User table to have a UserID key field with a foreign key relation to it in the Contact table. If so, you can do this:

Update C
Set email_address = U.FirstName + U.LastName + Right(C.email_address, Len(C.email_address) - CharIndex('@', C.email_address))
From contact C
Inner Join User U On U.UserID = C.UserID

If not, you'll have to try joining the email field to the name fields. If you do it this way, I'd run some QA checks on the user data to make sure there are no duplicate users with the same last name and first initial.

Update C
Set email_address = U.FirstName + U.LastName + Right(C.email_address, Len(C.email_address) - CharIndex('@', C.email_address))
From contact C
Inner Join User U On Left(C.email_address, CharIndex('@', C.email_address) - 1) = Left(U.FirstName, 1) + U.LastName


I've never worked with Sybase, so my sql above may need some tweaking to get it right.

[the325is]

Rawhide,

Foremost thanks for your help. I did modified the script a little but the problem that I am experiencing is the first and last name has so many character fields so there is a huge gap between the first and last name.
Secondly Sybase doesn't recognize "Len" can you let me know what purpose it serves.
Last I need to enter a period between the first and last name can you let me know where and how I would insert it.

Thanks again,
Wali

Update contact
Set C.email_address = U.first_name + U.last_name + Right(C.email_address,
- CharIndex('@', C.email_address))
From contact C, mtb_usr U
where U.usr_id = C.desktop_user_id
and C.cntct_id = 1852

[the325is]

Can you post same sample data?

I have supplied info for Rawhide hope that helps but let me know if you need additional information.

Thanks,
Wali

[Rawhide]

Len() is the Length function. It returns the length of the given text value.

By huge gap, do you mean that there are padded spaces on the fields? If so, you need to trim them off. In SQL Server, you could use the RTrim() and LTrim() functions to trim the right and left sides of the text. Some SQL engines also support the more generic Trim() which trims both sides. You may need to convert the datatype of the fields to a type that doesn't preserve padded spaces.

The period would simply go between the names.

So, it would look something like:

Update C
Set C.email_address = RTrim(LTrim(Convert(varchar, U.first_name))) + '.' + RTrim(LTrim(Convert(varchar, U.last_name))) + Right(C.email_address,
Len(C.email_address) - CharIndex('@', C.email_address))
From contact C, mtb_usr U
where U.usr_id = C.desktop_user_id
and C.cntct_id = 1852