Home » SQL & PL/SQL » SQL & PL/SQL » address query
address query [message #288006] Fri, 14 December 2007 05:14 Go to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
hi,
i have a table called address
whose structure is
adr number
street_number varchar2(50

there are recoords like

9999 0000045
9991 45
9992 0001045
9993 0010045

the user would enter say
45 on the front end java screen and the
query has to fetch
records of street_number 45 as well as 0000045
Re: address query [message #288007 is a reply to message #288006] Fri, 14 December 2007 05:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
where replace(yourcol, '0','') = '45'
Re: address query [message #288010 is a reply to message #288007] Fri, 14 December 2007 05:37 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
Hi
it is also fetching records like
0001650
Re: address query [message #288011 is a reply to message #288007] Fri, 14 December 2007 05:37 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
Sorry it is
0000450
Re: address query [message #288014 is a reply to message #288006] Fri, 14 December 2007 05:45 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

replace(yourcol, '0','') = '45'



If you use above then you get 45 for 450 too.

Try with TRUNC(yourcol)

Kiran.



Re: address query [message #288016 is a reply to message #288006] Fri, 14 December 2007 05:47 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SQL> select trunc(0000450 ),trunc(0000045),trunc(0010045) from dual;

TRUNC(0000450) TRUNC(0000045) TRUNC(0010045)
-------------- -------------- --------------
           450             45          10045



Regards,
Kiran.
Re: address query [message #288019 is a reply to message #288006] Fri, 14 December 2007 05:50 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ya, Of course, TRUNC will work for VARCHAR2 also.


SQL> select trunc('0000450' ),trunc('0000045'),trunc('0010045') from dual;

TRUNC('0000450') TRUNC('0000045') TRUNC('0010045')
---------------- ---------------- ----------------
             450               45            10045


Regards,
Kiran.
Re: address query [message #288022 is a reply to message #288011] Fri, 14 December 2007 05:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And if you had supplied an appropriate set of records I would not have supplied that answer. This is why we ask for representative test cases.
Re: address query [message #288023 is a reply to message #288019] Fri, 14 December 2007 05:56 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
No,
i need only 45 and 0000045
Re: address query [message #288024 is a reply to message #288022] Fri, 14 December 2007 05:56 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
sorry
Re: address query [message #288025 is a reply to message #288006] Fri, 14 December 2007 05:58 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

And if you had supplied an appropriate set of records I would not have supplied that answer. This is why we ask for representative test cases.


I do agree. At first i did not understand the question. I read it about five times. It was a just guess, that too after seeing your reply.

Kiran.
Re: address query [message #288026 is a reply to message #288006] Fri, 14 December 2007 06:00 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

No,
i need only 45 and 0000045


Again confusing, can you provide create table and insert table scripts with your expected output.

Kiran.
Re: address query [message #288029 is a reply to message #288026] Fri, 14 December 2007 06:06 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
CREATE TABLE ADDRESS
(
ADR NUMBER,
STREET_NUMBER VARCHAR2(20 )

)

the records are like

9999 45
9991 0000045
9992 0001045
9993 0010045

i want only adr values for street numbers 45 and 0000045
Re: address query [message #288031 is a reply to message #288029] Fri, 14 December 2007 06:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

i want only adr values for street numbers 45 and 0000045


where  STREET_NUMBER in ('45' and '0000045')

My point is that your requirements are still pretty vague.

However, 1 more guess
where to_number(address) = 45

[Updated on: Fri, 14 December 2007 06:29]

Report message to a moderator

Re: address query [message #288033 is a reply to message #288031] Fri, 14 December 2007 06:53 Go to previous messageGo to next message
hgriva
Messages: 17
Registered: December 2007
Junior Member
Thanks a lot for your help.
Re: address query [message #288035 is a reply to message #288006] Fri, 14 December 2007 07:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Using TO_NUMBER may result in error when the column contains non-numeric value. I would use LTRIM instead.
LTRIM( street_number, '0' )
Re: address query [message #288036 is a reply to message #288029] Fri, 14 December 2007 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why is the datatype of address_number varchar2?

[Edit: Ah, I see flyboy had the same thought.]

[Updated on: Fri, 14 December 2007 07:11]

Report message to a moderator

Re: address query [message #288037 is a reply to message #288035] Fri, 14 December 2007 07:13 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
There was no implication from the poster that the values would contain non-numeric data.
Re: address query [message #288040 is a reply to message #288006] Fri, 14 December 2007 07:45 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Using TO_NUMBER may result in error when the column contains non-numeric value. I would use LTRIM instead.

LTRIM( street_number, '0' )


How about TRUNC(street_number), ie where TRUNC(address,0) = 45



SQL> select trunc('0000045',0) from dual;

TRUNC('0000045',0)
------------------
                45



Hope this will work for both number and varchar2.

Kiran.
Re: address query [message #288041 is a reply to message #288040] Fri, 14 December 2007 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, why not just:
SQL> select to_number('0000045') from dual;
TO_NUMBER('0000045')
--------------------
                  45

1 row selected.

TRUNC does it implicitly.

Regards
Michel
Re: address query [message #288044 is a reply to message #288006] Fri, 14 December 2007 07:59 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

There was no implication from the poster that the values would contain non-numeric data.
So why does he store it in VARCHAR2? Yes, it may be bad design (as he is not interested in leading zeros). Or, as described in House numbering article, there is a possibility of non-numeric characters - see the Europe section (houses are sometimes numbered eg. 15a, 15b in my country).

Quote:

How about TRUNC(street_number), ie where TRUNC(address,0) = 45
Hope this will work for both number and varchar2.

Why do you not show it?
TRUNC (number). Note the word "number". Implicit typecasting to number is done, so no difference against TO_NUMBER.
Previous Topic: retrieve the missing numbers
Next Topic: error
Goto Forum:
  


Current Time: Mon Dec 05 14:39:34 CST 2016

Total time taken to generate the page: 0.05596 seconds