Subscribe to this thread
Home - General / All posts - Replacing text in a field with <NULL>
tonyw
616 post(s)
#17-Oct-20 23:06

[Edit to subject] Replacing <NULL> in a field with some text using a Transform template

Situation: I created a new text field named "Show" in a Table. I didn't use an expression when creating the new field in Edit > Schema. I'll use this new field for thematic formatting for instance selecting certain records based on a number of criteria then changing the value in Show to the word "yes".

I can fill the field in selected records with the Right Click on the field in a record > Copy to Selected. This is really handy.

However, in trying out the Transform templates, what Transform template do I use? "Replace" seems logical but I can't figure out how to select records using search for where the value of the field is NULL. I've tried NULL, "NULL", 'NULL", ISNULL, and also leaving the search field blank. If there is text already in some records for that field, I can successfully search for the target text and replace it. However not when the field is empty (NULL). I suppose I could use Right Click > Copy to Selected to pre-fill the field in all records with some dummy text characters. So this is more a question and exercise in trying out the new Transform templates.

The attached screenshot shows my setup in Transform > Replace. Clicking on Transform changes nothing, the field remains empty for all records.

Now if the field was of type Integer (int32) then using Transform > expression and simply typing in a number into the expression editor and clicking Transform, I can fill all (or the selected) fields that were previously empty (NULL) with the number. It's replacing NULL with text in a field of type varchar or nvarchar I can't figure out using a Transform template.

Attachments:
finding fields with Null values.jpg

tonyw
616 post(s)
#18-Oct-20 00:09

Testing some more, a combination of using Transform > Select and Edit > Select Inverse if needed then Transform > Expression works to fill empty fields with text. Note to self, wrap text with single quotes.

Dimitri


6,233 post(s)
#18-Oct-20 19:48

Open the table. Select all NULL fields (Select : NULL). Change the NULL field to whatever you want it to be. Right click on it and choose Copy to Selection.

Dimitri


6,233 post(s)
#19-Oct-20 11:21

See an example in this topic.

tonyw
616 post(s)
#19-Oct-20 17:05

Thanks Dimitri, Copy to Selection is a great feature.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.