Home » SQL & PL/SQL » SQL & PL/SQL » how to identify spaces
how to identify spaces [message #380092] Fri, 09 January 2009 02:13 Go to next message
Messages: 65
Registered: December 2008
Location: singapore

select distinct cpf_no,payee_name from ttas_invoice
where cpf_no is not null
and not exists (select 'X' from ar.hz_cust_accounts where orig_system_reference = cpf_no);

the above is my query
i had a cpf number :048893
in orig_system_reference the number exists with spaces but still its appearing as it is not exists.

i can i know that there are spaces in that number and how to rectify this .
i used trim but its not correct way to solve so please help me.
how to check how many spaces are there or how to set any variable when spaces are there.

this is an urgent issue.
Re: how to identify spaces [message #380094 is a reply to message #380092] Fri, 09 January 2009 02:38 Go to previous messageGo to next message
Messages: 7880
Registered: March 2000
Senior Member
If it is urgent, you should not rely on forums.

You were on the right track with your TRIM() function.
Hint: what happens when you remove the spaces? The LENGTH() of the string is reduced if spaces are removed!

So, the solution would be to compare the LENGTH() of the original result with the TRIMmed result
Re: how to identify spaces [message #380096 is a reply to message #380092] Fri, 09 January 2009 02:51 Go to previous message
Michel Cadot
Messages: 65144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous SQL topic:
Michel Cadot wrote on Tue, 06 January 2009 12:33
Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


[Updated on: Fri, 09 January 2009 02:51]

Report message to a moderator

Previous Topic: UTL_FILE vs External Tables (merged by bb)
Next Topic: Gererate Undo Query Logs corresponding to DDL/DML logs
Goto Forum:

Current Time: Mon Aug 21 01:44:32 CDT 2017

Total time taken to generate the page: 0.12203 seconds