To find the ascii value for a string

[JO]

Hello Guru's

I have requirement where I need to find the ascii value of the character in a string, if it is >127 then replace the character with '&'
Is it possible to achieve in Cognos?

Thanks
Jo

[Lynn]

Does your database have an ascii function? Perhaps an expression such as below might work. If you post what database you are using someone might be able to provide more specific advice.

Code Select Expand

if ( ascii ( [CharToCheck] ) > 127 then ( '&' ) else ( [CharToCheck] )

[JO]

lynn,

I am using SQL server and ASCII function finds the value for the first character. But i am looking for  a function which span through the entire string.

Thanks
Jo

[Lynn]

It sounds like that type of transformation might be better handled on the database side, perhaps as part of the ETL process or via a view.

If you think about how you would write that in a sql statement non-procedurally you can see it would be quite unwieldy depending on the length of the string and whether or not the string length is fixed or varied from row to row.

Perhaps sql server has a translate function that you can nest the ascii function within?

Fundamentally your question is more a database question than a Cognos question. I am not aware of a Cognos built-in function that will do this for you.