Query required

bharath18 · Posted: Wed Feb 10, 2010 9:00 pm

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

Craq Giegerich · Posted: Wed Feb 10, 2010 9:26 pm

WHERE SOME_X_COLUMN = 'A9999'

dick scherrer · Posted: Wed Feb 10, 2010 10:58 pm

Hi Craig,

Tough morning lots of places . . .

Craq Giegerich · Posted: Wed Feb 10, 2010 11:13 pm

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.

bharath18 · Posted: Thu Feb 11, 2010 11:24 am

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????

Escapa · Posted: Thu Feb 11, 2010 11:35 am

Yes your query is wrong.

I don't understand why you have used ORs. It should be AND I guess.

dick scherrer · Posted: Thu Feb 11, 2010 11:37 am

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. . .

Kjeld · Posted: Thu Feb 11, 2010 4:10 pm

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.

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.

dick scherrer · Posted: Sun Feb 14, 2010 1:09 am

Hello,

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!!

dick scherrer · Posted: Sun Feb 14, 2010 4:52 am

Hello Kylash,

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 ............

dick scherrer · Posted: Sun Feb 14, 2010 11:29 pm

Hello,