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: 64618
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: sql%rowcount difference in two update statements.
Next Topic: how to extract line/sting from long
Goto Forum:

Current Time: Tue Mar 28 14:49:30 CDT 2017

Total time taken to generate the page: 0.11735 seconds