How to find blank/ tab character string oracle SQL
How to find blank/ tab character string oracle SQL
|
1 2 3 |
SELECT UPPER(ADDRESS1) FROM PS_ADDRESSES WHERE regexp_like(ltrim(UPPER(ADDRESS1)), '[[:space:]]$'); |
How to replace Line Feeds, Carriage Returns and tabs are the usual culprits and cannot be removed using the standard orLTrimRTrim Functions. You can remove them with the following:
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(StringCharacter, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))
If you encounter more white space characters that can’t be removed with the above then try one or all of the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--How to replace NULL Replace([YourString],CHAR(0),''); --How to replace Horizontal Tab Replace([YourString],CHAR(9),''); --How to replace Line Feed Replace([YourString],CHAR(10),''); --How to replace Vertical Tab Replace([YourString],CHAR(11),''); --How to replace Form Feed Replace([YourString],CHAR(12),''); --How to replace Carriage Return Replace([YourString],CHAR(13),''); --How to replace Column Break Replace([YourString],CHAR(14),''); --How to replace Non-breaking space Replace([YourString],CHAR(160),''); |
References:
http://pplsoftlearner.blogspot.com/2016/
http://psacake.com/web/kn.asp