Showing results for 
Search instead for 
Do you mean 

Removing Line Breaks From Text String Attributes

by Technical Evangelist on ‎04-13-2017 09:29 AM - edited on ‎04-13-2017 01:03 PM by Technical Evangelist (653 Views)

Question

We recently discovered non-printing line feeds in some of our Oracle text data fields.  We would like to find a way to either replace them or trim them.  Is there a way to use Update Attributes to trim out these leading line feeds?

Answer

GeoMedia can ingest data to Read-Write warehouses from a variety of data sources which often can introduce hidden characters such as line breaks (<Ctrl> <Enter>) in text field values from the originating data. Unfortunately there is no one function that can generically remove all non-printable characters. However, if the type of character is known, you may be able to use the REPLACE function. Most non-printable characters have keyboard sequences that can be used to generate the condition.

For example a line break is often represented by a <Ctrl> <Enter> key board stroke. If the key stroke sequence is known, you can use the REPLACE function.

 

Syntax:
REPLACE(Text, Search_string, Replace_string, Start, Occurence, ExactMatch)

The blue parameters above are optional.

 

Simple Example:
REPLACE(Input.POLE_TYPE, "GAL", "METAL")
The expression above would replace all occurrences of "GAL" with "METAL"

 

We could try using the <Ctrl> <Enter> as the Search string by press and hold of the <Ctrl> key and press <Enter> .  The <Ctrl> <Enter> will need to be enclosed in matching quotes.  The expression will look strange as the expression string will wrap to the next line and replace it with nothing (indicated by beginning and ending quotes)…

 

Example:
REPLACE(Input.NAME,"
","")

 

Notice how when you press and hold the <Ctrl> key and press <Enter> how a new line break is inserted into the expression. A possible better replace expression of line breaks line strings with standard spacing would be:

 

TRIM(REPLACE(Input.NAME,"
"," "))

 

The TRIM in the expression removes any extra spaces that may not be needed at the end of a string.

Contributors