SELECT STATEMENT HELP

[jobillborf]

Hi, I am using version 9 connected to a oracle data base.

We have a field called hydtid. I would like to extract all the records that are in this format.

J12-1223 In other words the field starts with a letter (J) then 2 digits (12) then a dash (-) then four digits (1223).

thank you

 

[Turkbear]

Hi,
If just the # of characters is the key ( and not if they are actually numeric digits, you can use:

Select * from table where
hydtid like 'J__-____';

( 2 underlines and 4 underlines - Oracle's single character wildcard)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kskid]

How about

left({table.hydtid},4) like "J??-"

-lw

 

[lbass]

How about:

left({table.hydtid},1) = 'J' and
instr({table.hydtid},"-") = 4 and
len({table.hydtid}) = 8

This assumes that there are always numbers in positions 2,3, and 5 to 8.

-LB

 

[Turkbear]

Hi,
Sorry, I provided Oracle's syntax..

( You can use that in a Command Object)..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Tweak LB's formula (and Turk's might work if you create a SQL Expression):

left({table.hydtid},1) = "J"
and
isnumeric(mid({table.hydtid},2,2)
and
mid({table.hydtid},4,1) = "-"
and
isnumeric(mid({table.hydtid},5,4)

LB's will fail in many instances (e.g.: JQQ-QQQQ), the above should be correct, or at least much closer.

-k