Can this be done - 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 2008 Forums

Transact-SQL (2008)

Can this be done

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-07 : 13:20:53

I am trying to write a simple query to update the values in a table

Update MyTable
Set DestSortCode = CASE ISNULL(DestSortCode, '') WHEN '' then NULL ELSE RIGHT('000000' + REPLACE(DestSortCode,'-',''), 6) END
,DestAccountNumber = CASE ISNULL(DestAccountNumber, '') WHEN '' THEN NULL ELSE RIGHT('00000000' + DestAccountNumber , 8)END
,DestAccountName = CASE ISNULL(DestAccountName, '' ) WHEN '' THEN NULL ELSE UPPER(LEFT(DestAccountName + SPACE(18), 18)) END
,DestBankRef = CASE ISNULL(DestBankRef, '') WHEN '' THEN NULL ELSE UPPER(LEFT(DestBankRef + SPACE(18), 18)) END

I also needto check that DestAccountName only contains valid charactors. I have a function called CheckBACSValidChar which has a varchar parameter and returns a varchar. Is it possible ( with the right syntax ) to do something similar to

Update MyTable
Set DestSortCode = CASE ISNULL(DestSortCode, '') WHEN '' then NULL ELSE RIGHT('000000' + REPLACE(DestSortCode,'-',''), 6) END
,DestAccountNumber = CASE ISNULL(DestAccountNumber, '') WHEN '' THEN NULL ELSE RIGHT('00000000' + DestAccountNumber , 8)END
,DestAccountName = CASE ISNULL(DestAccountName, '' ) WHEN '' THEN NULL ELSE UPPER(LEFT(CheckBACSValidChar(DestAccountName) + SPACE(18), 18)) END

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-07 : 14:50:09

Resolved

Issue was function was in a different db .. DUH !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources