How to recognize strings containing nbsp

Word Processors and Web forms often use the non-breaking space character (nbsp) to prevent line breaks. This character looks the same as a normal space,…

OpenText profile picture
OpenText

August 29, 20182 minute read

Pfeile aus Beton, gestapelt, 3D Rendering, digital generiert, Business, Konzept, Idee, Symbol, Richtung, verschieden, farbig, unterschiedlich, Hinweis, verwirrend, uneinig, Meinungsverschiedenheit, Minimalismus, Einfachheit

Word Processors and Web forms often use the non-breaking space character (nbsp) to prevent line breaks. This character looks the same as a normal space, but uses a different Unicode character with its own UTF-8 encoding.

If you inadvertently cut and paste an nbsp character in your database strings, any search criteria using normal spaces will not locate strings containing an nbsp character.

This quick tip demonstrates how to recognize strings containing nbsp, as well as how to replace this character with a “normal” space.

In the following sample table the row with ID=3 contains an nbsp space (Unicode 00A0):

DROP TABLE IF EXISTS public.mytable;
CREATE TABLE public.mytable(id, txt) AS
SELECT 1, 'My String' UNION ALL
SELECT 2, 'My Other String' UNION ALL
SELECT 3, U&'My nbsp\00A0String' UNION ALL
SELECT 4, 'My Last String'
;

At a first sight, the row with ID 3 looks similar to the other rows:

SELECT * FROM public.mytable;
id | txt
----+-----------------
1 | My String
2 | My Other String
3 | My nbsp String
4 | My Last String
(4 rows)

But a search criteria containing normal spaces won’t locate the row with ID 3:

SELECT * FROM public.mytable WHERE txt LIKE '% String';
id | txt
----+-----------------
1 | My String
2 | My Other String
4 | My Last String
(3 rows)

Run the following command to determine if a column contains an nbsp character:

SELECT id, txt, ISUTF8(txt),
CASE WHEN INSTR(txt, U&'\00A0') > 0 THEN 'Yes' ELSE 'No' END AS
Contains_nbsp
FROM public.mytable ;
id | txt | ISUTF8 | Contains_nbsp
----+-----------------+--------+---------------
1 | My String | t | No
2 | My Other String | t | No
3 | My nbsp String | t | Yes
4 | My Last String | t | No
(4 rows)

To replace the nbsp character with a normal space you can either UPDATE the original table or create a new one. The following example shows how to create a new table and replace the nbsp character:

DROP TABLE IF EXISTS public.mynewtable;
CREATE TABLE public.mynewtable AS /* +direct */
SELECT id, REPLACE(txt, U&'\00A0', ' ') AS txt
FROM public.mytable
;

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

4 minute read

Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

4 minute read

SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

3 minute read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.