Add text to db field - CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add text to db field - CASE

Author

Topic

wiltjer
Starting Member

11 Posts

Posted - 2007-03-15 : 11:20:59

Hello,

I have the following problem. Presum I have a field called contact_mail. I want to check if the field is empty.

If that is true then i want to get the faxnumber from a field called contact_fax . And then i want to put @faxmaker.com behind it in a new field called faxmail.

Something like this contact_fax:123456 - faxmail:123456@faxmaker.com.
I've made the following case.

CASE contact_mail
WHEN 'NULL' THEN 'contact_fax@faxmaker.com'
END AS faxmail

But this doesn't work, does anyone got a hint?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:32:24

update table1
set faxmail = CASE WHEN contact_mail is null then 'contact_fax@faxmaker.com' else contact_fax + '@faxmaker.com' end

Peter Larsson
Helsingborg, Sweden

wiltjer
Starting Member

11 Posts

Posted - 2007-03-15 : 11:47:53

Is the UPDATE strictly necessary? Because i've made another CASE:

CASE c.cmp_fctry
WHEN 'NL' THEN 'The Netherlands'
END AS landnaam,

And this one works like a charm.

Because now I get an error for UPDATE.

The full query is as following:

select *

CASE c.cmp_fctry
WHEN 'NL' THEN 'The Netherlands'
END AS landnaam,

UPDATE absences
SET faxmail = CASE WHEN ctp1.cnt_email IS NULL
THEN 'c1.cmp_fax@faxmaker.com' ELSE c1.cmp_fax + '@faxmaker.com' END

FROM absences a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 11:54:06

I give up.

Peter Larsson
Helsingborg, Sweden

wiltjer
Starting Member

11 Posts

Posted - 2007-03-15 : 11:56:58

Is it really so bad with me?? I know i'm a n00b, just trying my best.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-03-15 : 12:30:20

Yes, it is really that bad. You have almost EXACTLY what Peter told you, except you have your correct column names.

PETER: CASE WHEN contact_mail is null then 'contact_fax@faxmaker.com' else contact_fax + '@faxmaker.com' end

YOU: CASE WHEN ctp1.cnt_email IS NULL THEN 'c1.cmp_fax@faxmaker.com' ELSE c1.cmp_fax + '@faxmaker.com' END

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 12:31:54

1) Where is the comma after the asterisk?
2) An UPDATE statement in a SELECT query?

Peter Larsson
Helsingborg, Sweden

wiltjer
Starting Member

11 Posts

Posted - 2007-03-16 : 03:48:58

Oke i'm not that stupid

.

I didn't post all the fields i selected in the SELECT because it was such a long list.
And i don't want to update the database with that new field faxmail. I only want to use the result in event manager to automaticaly send a mail to a company.

And I know there should be a comma behind the asterix in de SELECT query.

So therefore I ask again is this possible in another way then with the update.

wiltjer
Starting Member

11 Posts

Posted - 2007-03-16 : 04:43:38

Well i'v worked it out this was all i had to do:

CASE
WHEN ctp1.cnt_email IS NULL
THEN c1.cmp_fax + '@faxmaker.com'
END AS faxmail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:49:22

What if ctp1.cnt_email is NOT null?

Peter Larsson
Helsingborg, Sweden

wiltjer
Starting Member

11 Posts

Posted - 2007-03-16 : 06:59:14

In exact event manager i filled in the TO field the contact_mail database field and in the CC field the faxmail database field. So if the first is NULL then the faxmail field gets filled in.

Now all i have to do is remove the spaces between '938472834 @faxmaker.com'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources