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

Three key aspects of being a threat hunter  

Three key aspects of being a threat hunter  

In today’s digital landscape, the role of a threat hunter has become indispensable. As cyber threats grow increasingly sophisticated, the need for professionals who can…

5 minute read

Fax and figures – automate your fax processes for maximum productivity

Fax and figures – automate your fax processes for maximum productivity

Manual fax processing isn’t scalable

4 minute read

Insights on AI and ISO 20022: OpenText helps shape the narrative at the Payments Canada Summit

Insights on AI and ISO 20022: OpenText helps shape the narrative at the Payments Canada Summit

The 2024 Payments Canada Summit recently concluded, bringing together industry leaders, innovators, and key stakeholders to discuss the most recent trends and insights in payments….

5 minute read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.

Sign up