View previous topic :: View next topic
|
Author |
Message |
bharath18
New User
Joined: 12 Sep 2006 Posts: 15 Location: Chennai, Tamilnadu, India
|
|
|
|
Hi,
I want to do a query on some x column where that x column is 5 char length, but i want to find in particular the A9999.
i.e first character should be alphabetic and other 4 characters should be numeric, is that possible?
if so please let me know.
Thanks,
Bharat |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
WHERE SOME_X_COLUMN = 'A9999' |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hi Craig,
Tough morning lots of places . . .
Quote: |
i.e first character should be alphabetic and other 4 characters should be numeric, |
@Bharat
Possibly by using a substring for each "piece" of the column. . . |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Last time I checked A is alphabetic and 9999 are numeric. If Bharat wants any alphabetic followed by any 4 numerics then that is a different matter, more complicated and very inefficient. |
|
Back to top |
|
|
bharath18
New User
Joined: 12 Sep 2006 Posts: 15 Location: Chennai, Tamilnadu, India
|
|
|
|
hey i was not able to get the query,
i tried the below one a lengthy one...
SELECT *
FROM xxxxxx WHERE
SUBSTR(ABC,1) IN ('A','B'..(removed in b/w).. 'Z') AND
SUBSTR(abc,2) IN ('1','2','3','4','5','6','7','8','9','0') OR
SUBSTR(abc,3) IN ('1','2','3','4','5','6','7','8','9','0') OR
SUBSTR(abc,4) IN ('1','2','3','4','5','6','7','8','9','0') OR
SUBSTR(abc,5) IN ('1','2','3','4','5','6','7','8','9','0');;;;;
but i get irrelevant rows...
such as
aj123
ab345
cj222
I should get the result as
a1234
b2345
c3456.
I could not get the desired result, is that i am querying wrong???? |
|
Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007 Posts: 1399 Location: IL, USA
|
|
|
|
Yes your query is wrong.
I don't understand why you have used ORs. It should be AND I guess. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Why did the code change from AND to ORs?
I believe only rows that meet all conditions should be selected, but the ORs do not provide this restriction.
Also, this should only run across a small number of rows. . . This code will perform terribly. . . |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
It can't be done with SQL in an effecient way, but will always force DB2 to do a scan of all data pages.
I will suggest that you omit the criteria from your select, and conditionally processes the rows that meets the desired condition.
If this is a one-off selection you might get the aforementioned code to work, provided you replace the ORs with ANDs. |
|
Back to top |
|
|
Kylash V
New User
Joined: 13 Feb 2010 Posts: 5 Location: Chennai
|
|
|
|
Hi Bharath,
You could try the following query
SELECT *
FROM XXXX
WHERE ABC >= 'A0000'
AND ABC <= 'z9999'
I believe DB2 does character-by-character ASCII comparison to achieve the final result.
So, while A1111 would fall in this list, 12345 would be < A0000 and be out of range; same with 99999.
However, i am not sure of Aa234. I would expect expect it to be greater than A0000 but also greater than z9999 and hence not fall into this category. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
You could try the following query
SELECT *
FROM XXXX
WHERE ABC >= 'A0000'
AND ABC <= 'z9999' |
Please do not post suggestions/solutions that are not tested. . .
The posted code will execute but will not provide the requested result (on neither an ASCII nor an EBCDIC collating sequence.
Also, typically db2 on the mainframe does not use ASCII. Almost all mainframe processing is EBCDIC. On the mainframe 12345 is greater than A0000. . . |
|
Back to top |
|
|
Kylash V
New User
Joined: 13 Feb 2010 Posts: 5 Location: Chennai
|
|
|
|
Hi Dick
My apologies for having presented an untested solution. And thanks for letting me know that DB2 does not take the ASCII sequence durting comparisons.
Thanks again!! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello Kylash,
Quote: |
And thanks for letting me know that DB2 does not take the ASCII sequence durting comparisons. |
You're welcome.
However, you also need to be aware that "range checking" cannot work for this requirement. This requirement deals with the type of the data bytes not the combined field content (1st byte must be alpha, the others numeric). |
|
Back to top |
|
|
jitu852
New User
Joined: 30 Apr 2007 Posts: 27 Location: Hyderabad
|
|
|
|
Replace all 'OR' by 'AND' in your original query then it will work fine ............ |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Replace all 'OR' by 'AND' in your original query then it will work fine ............ |
It is best not to post solutions aleady given in the topic. . . As in:
Quote: |
Yes your query is wrong.
I don't understand why you have used ORs. It should be AND I guess. |
and
Quote: |
Why did the code change from AND to ORs?
I believe only rows that meet all conditions should be selected, but the ORs do not provide this restriction.
|
|
|
Back to top |
|
|
|