Replace always replacing null values

Question

Why does

left(FIELD, replace(nullif(charindex('-', FIELD), 0), null, len(FIELD)))

always return null? The idea behind the query is that if charindex() returns 0, then convert the results into null, then convert the null into the length of the field. So if '-' does not exist, show the whole string. For some reason it makes every row equal null.

Thank you.

Answer 1

So, if field contains "-", show length. If no "-", show field?

CASE
    WHEN FIELD NOT LIKE '%-% THEN FIELD
    ELSE CAST(LEN (FIELD) AS varchar(4))
END

You need the cast to avoid datatype precedence

Flip the condition if you want NULL FIELD to work differently (it will go to the ELSE clause)

Answer 2

Because REPLACE() returns NULL if any one of the arguments is NULL. Says so in the docs.

Do this instead:

select isnull(nullif(charindex(' ',field),0),len(field))

Answer 3

When you say then convert the null into the length of the field, do you mean convert Nulls into a series of spaces like so:

Select
    Case
    When Field Like '%-%' Then Replicate(' ', Len(Field))
    Else Field
    End