String update - 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

SQL Server 2005 Forums

Transact-SQL (2005)

String update

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-01-26 : 05:31:23

I have a table

Salutation Forename, MiddleName, Surname, CorrespondenceSalutation

Mr Fred A Spires (null)
Mrs Dora Jarr (null)

I want to update the table so that

CorrespondenceSalutation field = Salutation + " " + Forename + " " + MiddleName + " " + Surname

if the fields Middlename is not null or blank. Otherwise

CorrespondenceSalutation = Salutation + " " + Forename + " " + Surname

so that the resultant table gives

Salutation Forename, MiddleName, Surname, CorrespondenceSalutation

Mr Fred A Spires Mr Fred A Spires
Mrs Dora Jarr Mrs Dora Jarr

How can I do this please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 05:48:06

[code]UPDATE Table1
SET CorrespondenceSalutation = RTRIM( COALESCE(Salutation + ' ', '')
+ COALESCE(Forename + ' ', '')
+ COALESCE(MiddleName + ' ', '')
+ COALESCE(Surname, '')
)[/code]

E 12°55'05.63"
N 56°04'39.26"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-01-26 : 05:53:33

Much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources