Search for number in varchar column

[Horace]

Hi All,

I have a varchar2 column 'Address_Line_1' and I need to identify all the rows that have a number somewhere in that column.

So... 1 Western Avenue ... is selected
But.. The Manor ... is not selected

Yes, most Address lines will have a number, but there is a valid reason.

Can anyone suggest a good way of doing this.

i was thinking of using translate.....

Thanks.

[KenEwald]

What version are you on? If you're on 10, I'd say using regexp (regular expression) is the way to go...

[John Spencer]

If you can't use regexp, I would be inclined to go with something like:

SELECT col FROM t
WHERE INSTR(col, '1') <> 0 or
      INSTR(col, '2') <> 0 or
      INSTR(col, '3') <> 0 or
      INSTR(col, '4') <> 0 or
      INSTR(col, '5') <> 0 or
      INSTR(col, '6') <> 0 or
      INSTR(col, '7') <> 0 or
      INSTR(col, '8') <> 0 or
      INSTR(col, '9') <> 0

You are going to be full scanning anyway, at least the ORs will short circuit on the evaluation.

John

[slimdave]

select col from t
where instr(translate(col,'123456789','000000000'),'0') > 0

[Horace]

Thanks Guys,

Tried both, same plan, more or less the same timings BUT John's code returns 48,333,191 rows and Dave's code returns 48,334,913 rows.

I'm having trouble spotting the difference......... Help !!

[Alien]

Hi,

probably the 0's. John didn't have them in the OR.

Regards,

Arian

[slimdave]

W00t!

[marist89]

When in doubt, slimdave is always right.

[Alien]

W00t!

Am I getting old already?

[slimdave]

When in doubt, slimdave is always right.

... especially about politics.