address query [message #288006] |
Fri, 14 December 2007 05:14 |
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 #288016 is a reply to message #288006] |
Fri, 14 December 2007 05:47 |
|
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 |
|
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 |
pablolee
Messages: 2882 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 #288025 is a reply to message #288006] |
Fri, 14 December 2007 05:58 |
|
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 |
|
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 |
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 |
pablolee
Messages: 2882 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 #288040 is a reply to message #288006] |
Fri, 14 December 2007 07:45 |
|
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 #288044 is a reply to message #288006] |
Fri, 14 December 2007 07:59 |
flyboy
Messages: 1903 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.
|
|
|