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
;
