Printable View
Is there a way in Oracle SQL that I can look at a position of a column and determine if that data is an integer or not?
The column is defined as a varchar2 and I know I need to subscript into it(second position for 1 byte) but I don't know how to test for 0-9 without coding 0,1,2,3,4,5,6,7,8,9.
The data would look something like "CJE20020" and I want to include data with non-interger values(i.e. include the amount for this activity since the second position is a "J").
I am not clear what you want ..
Extract second char and
and use any number function to find it is number or not
If not number it will error out
example
select abs(itmid) from ccs_orders where rownum < 2
*
ERROR at line 1:
ORA-01722: invalid number
If it is number
SQL> select abs(acweek) from ccs_orders where rownum < 2;
ABS(ACWEEK)
-----------
1
do you mean something like ...
where translate(substr(my_column,2,1),'1234567890','0000000000')) = '0'
This might be what I am looking for. I'll give it a shot and see what get. Thank you.Quote:
Originally posted by slimdave
do you mean something like ...
where translate(substr(my_column,2,1),'1234567890','0000000000')) = '0'
Why are you limited to using only SQL? Test for an integer? You could use the mod function. mod (number/1) not equal to zero --> number is not an integer. Create a function, pass in the number.
Well to clarify things a bit, I won't be translating anything. I'm just trying to select data where the second position is alphabetical(i.e. a-z) so is there a way I can specify that without actually typing out all the letters in the alphabet?
I know I could do it with something like .. SUBTR(column-name,2,1) in 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' but I'm assuming there some decent class function or something I could use?
translate(upper(substr(my_column,2,1)),'QWERTYUIOPASDFGHJKLZXCVBNM','AAAAAAAAAAAAAAAAAAAAAAAAAAA')) = 'A'
would do it (check the number of A's, not sure i got it right), or you could check for an ascii code BETWEEN two appropriate values.
Can you tell me what's the purpose of arranging the values "QWERTYUIOPASDFGHJKLZXCVBNM" in that order? I counted the number of A's and there are 27 of them. Suppose to be 26?
Jeez, ever look at the keyboard? What is the first letter in the top left corner?
:D That's why I am a junior member....
easy to type.Quote:
Originally posted by rkeokitv
Can you tell me what's the purpose of arranging the values "QWERTYUIOPASDFGHJKLZXCVBNM" in that order?
Well, being the trivia master I am, sometimes the standard keyboard is referred to as the qwerty keyboard. Some people think the Dvorak (named after its inventor, not the layout) is better. If you like the Dvorak keyboard, then you'll probably be speaking Esperanto someday.
'A' if you're French ! ! !Quote:
Originally posted by stecal
Jeez, ever look at the keyboard? What is the first letter in the top left corner?
SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN chr(65) and chr(90)
- Nandu
I too am a master of trivia, and i know that the original tests which "proved" the dvorak keyboard to be superior were carried out by the US Navy in 1944, tests that were carried out by a time-and-motion expert Lieutenant-Commander August Dvorak -- guess what? He owned the patent on the Dvorak keyboard layout.Quote:
Well, being the trivia master I am, sometimes the standard keyboard is referred to as the qwerty keyboard. Some people think the Dvorak (named after its inventor, not the layout) is better. If you like the Dvorak keyboard, then you'll probably be speaking Esperanto someday.
Tests carried out by the General Services Administration in '56 found qwerty to be superior, which pretty much killed interest in retraining people to dvorak.
There must be something better than QWERTY (or QWERTZ or AZERTY etc) since that layout was invented to SLOW DOWN typists in the early days when the primitive mechanical typewriters could not keep up.
(In case you were wondering - NO, I don't personally remember that period! Come to think of it - more triva - earliest "typewriting" known? - about 1'500 BC!)
. . . . and the k/b on the English language Linotype type-setting machine was ETAOIN. The dies had to fall into place, so the common letters were placed so that they travalled the shortest distance.
I guess Nandu gave us very good query
SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN chr(65) and chr(90)
You don't need to mention a..z in query
Hm, I don't know about you, but I would never want to decypher what that that query is supposed to mean if I would find it in my shared pool. Isn't the following variant much more "human"?Quote:
Originally posted by raghud
I guess Nandu gave us very good query
SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN chr(65) and chr(90)
You don't need to mention a..z in query
SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN 'A' and 'Z'
A touch of Genius ! jmodic you have ur own ways of refining! :)
- Nandu
DITTO!!!:)Quote:
Originally posted by nandu
A touch of Genius ! jmodic you have ur own ways of refining! :)
- Nandu
Hm, I did not get you .What's wrong with that query.
Okay if I have a DECODE in the statement, can I use the BETWEEN function to finc the character value? For example:
DECODE(SUBSTR(RES.ACTIVITY_ID,2,1)
,'1','Water'
,'2','Solid Waste'
,'3','Drainage/Wastewater'
,'5','Shared'
,'Unknown'
)
In some cases while building the sql statements dynamically in some front end languages, we need to concatenate strings . While concatenation it is very difficult to merge strings like 'A' or '1'. Because single quote means different. In that case it is very easy to use chr(65) like to build dynamic SQL's . Any way it depends case by case.:D
for example
string str1
str1='SELECT ename FROM Emp
WHERE upper(substr(ename,2,1)) BETWEEN'
string str2
str2 = str1 + ''A'' + ' AND ' + ''B''
It will trough an error
str2 = str1 + 'char(65)' it is easy so that we can get our whole select statement for immediate execute.
Hm, wonder why they invented a concept of BIND VARIABLES....
If I put the Between clause in quotes, it tries to interpret it as a literal. Do you know exactly how to code a between in the decode? I can't find any literature on it specifically.
SELECT
DECODE(UPPER(SUBSTR(ACTIVITY_ID,2,1))
,'1','Water'
,BETWEEN 'A' and 'Z','Water'
,'2','Waste'
,'3','ToolA/ToolB'
,'5','Shared'
,'Unknown'
) fund
,activity_id
from
SCHEMA.TABLE_INFO
where table_id like 'C%'
Try this way !
SELECT case when UPPER(SUBSTR(ename,2,1)) = '1' then 'Fire'
when UPPER(SUBSTR(ename,2,1)) BETWEEN 'A' and 'M' then 'Water'
when UPPER(SUBSTR(ename,2,1)) BETWEEN 'L' and 'Z' then 'Waste' else 'Unknown' end from emp
HTH
- Nandu