|
View previous topic :: View next topic
|
| Author |
Message |
muffirulz
New User
Joined: 14 Sep 2005
Posts: 74
Location: Atlanta, (USA)
|
|
|
|
Hi
Suppose I have a field whose definition is x(16). Now I only want to select those rows where the length of the data is > 9.
For example if I have data
12345
123456789
451728907354
152709038409
123456
142638394038101
Then using above data my query should return only following three rows
451728907354
152709038409
142638394038101
Please help me out with this.
Thanks, |
|
| Back to top |
|
 |
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
muffirulz,
Using your example
| Code: |
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL
(
C_DATA_FIELD CHAR(15)
)
;
INSERT?.
SELECT C_DATA_FIELD
FROM SESSION.TEMP_TBL
;
+-----------------+
| C_DATA_FIELD |
+-----------------+
1_| 12345 |
2_| 123456789 |
3_| 451728907354 |
4_| 152709038409 |
5_| 123456 |
6_| 142638394038101 |
+-----------------+
SELECT C_DATA_FIELD
FROM SESSION.TEMP_TBL
WHERE SUBSTR(C_DATA_FIELD,10,5) <> ' '
;
+-----------------+
| C_DATA_FIELD |
+-----------------+
1_| 451728907354 |
2_| 152709038409 |
3_| 142638394038101 |
+-----------------+
|
or
| Code: |
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL
(
V_DATA_FIELD VARCHAR(15)
)
;
INSERT?.
SELECT V_DATA_FIELD
FROM SESSION.TEMP_TBL
;
+-----------------+
| V_DATA_FIELD |
+-----------------+
1_| 12345 |
2_| 123456789 |
3_| 451728907354 |
4_| 152709038409 |
5_| 123456 |
6_| 142638394038101 |
+-----------------+
SELECT V_DATA_FIELD
FROM SESSION.TEMP_TBL
WHERE LENGTH(V_DATA_FIELD) > 9
+-----------------+
| V_DATA_FIELD |
+-----------------+
1_| 451728907354 |
2_| 152709038409 |
3_| 142638394038101 |
+-----------------+
|
This what you?re looking for ?
Dave |
|
| Back to top |
|
|
T-REXDB2
New User
Joined: 28 Apr 2005
Posts: 11
Location: USA
|
|
|
|
| muffirulz wrote: |
Hi
Suppose I have a field whose definition is x(16). Now I only want to select those rows where the length of the data is > 9.
For example if I have data
12345
123456789
451728907354
152709038409
123456
142638394038101
Then using above data my query should return only following three rows
451728907354
152709038409
142638394038101
Please help me out with this.
Thanks, |
Extremely simple answer to extremely simple question:
SELECT *
FROM SYSIBM.SYSTABLES
WHERE LENGTH(NAME) > 9
Hope this helps, muchly!!
;-] |
|
| Back to top |
|
|
|
|
