>>>Hi everybody,
>>>
>>>I have the following replace statement:

>>>replace Street with m.lcNewStreetName, ;
>>>               ccode with m.lcNewCode, ;
>>>		town with m.lcNewTown, ModiType with 'C', ;			
>>>		(.cWorkField) with .cReplVal ;
>>>		for upper(ccode+town+Street+str(StNum,4)+StNumExt+Unit)= ;
>>>m.lcCond in BldMstr

>>>
>>>Set exact is set to off, because I want to replace old street names with the new street names regardless of StNum or StNumExt. However, if StNumExt is NULL, this replace command doesn't work.
>>>
>>>We have situs index in the table, which is exactly

>>>upper(ccode+town+Street+str(StNum,4)+StNumExt+Unit)

>>>
>>>Could you please advise, what should I do, to make the replace command work for NULL in Unit or StNumExt as well?
>>>
>>>Thanks a lot in advance.
>>
>>NULL values propagate in most calculations. The concatenation (+) involving a null value will produce NULL for the entire result.
>>
>>Perhaps you can convert NULL to an acceptable replacement with NVL(). For instance: NVL(CharField4, space(4)). If the field is NULL, you will get space(4) instead.
>>
>>HTH, Hilmar.
>
>Hi Hilmar,
>
>This basically confirms my thoughts. I'm thinking about using this index expression instead:
>

>upper(ccode+town+street+ ;
>  nvl(str(StNum,4), space(4))+nvl(StNumExt, space(4))+nvl(Unit, space(6)))

NULL values can be tricky, in that, if ANY PART of an expression is null, the result is null (except for special functions like isnull() or nvl()).

Indeed, you should take care with your indices. If you don't want the entire index key to be NULL, you should use nvl() to replace with an acceptable non-null value.

Hilmar.