Aritmectic Operator in Formula?

Hi all,

this formula doesn't work as calculated column but WHY?

Column [MAIN]: VARCHAR(150)

VALUE : 'blabla - zobzob'

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])) WORKS and return 'blabla -'

BUT

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])-2) DOESN'T WORK in a Formula BUT well in a select clause...

Any Idea?

Thanks

In what way does it 'not work'...?

It seems to work ok... (SQL Server 2000)

create table #x (main varchar(20) not null)

insert #x select 'blabla - zobzob'

alter table #x add b as SUBSTRING(MAIN,1,CHARINDEX('-',MAIN)-2)

select * from #x

drop table #x

main b
-- --
blabla - zobzob blabla

/Kenneth

|||

Yes indeed, it works with a sql script but NOT manually in SQL Server 2000... Very Strange...

Tks a lot

|||

Hmm.. what do you mean by 'manually'..?

In my mind T-SQL code in QA is as 'manual' as you can get..?

/Kenneth

|||With the editor provided in SQL Server 2000...|||

You mean Enterprise Manager?

I'd suggest as a solution that you don't use it for other purposes than viewing.
For one thing it doesn't allow you any transactional control when you perform write operations, and some things it does in an awkward way, which adds up to that it takes unnecessary long time and/or large amount of resources to complete.

Make the wrong choice in EM and chances increase that you're looking for the latest good backup, instead of just typing ROLLBACK in QA, when something unexpected happened with the latest change. This ability is due to the fact that you can start your scripts with BEGIN TRAN (don't forget it) before actually performing the change. This is something that EM doesn't give you. =:o/

EM is imo not a fitting tool for database change management. (perhaps with the exception of user and roles management)

Do all your (data/schema) changes in Query Analyzer by controlled scripts instead.

/Kenneth