0

I have a column. The data type is VARCHAR2. What I am trying to do is write a query to catch only the records that contain a string either in the beginning or end or in the middle. Below is a sample.

Column1
AB786GH
7890651
387A656
723068X
0

2 Answers 2

0

... in the beginning or end or in the middle

Basically, anywhere.

There are various ways you could do that, e.g. using instr function which returns a positive number (position of the string you're searching for):

SQL> with test (column1) as
  2    (select 'AB786GH' from dual union all
  3     select '7890651' from dual union all
  4     select '387A656' from dual union all
  5     select '723068X' from dual
  6    )
  7  select column1
  8  from test
  9  where instr(column1, '78') > 0;

COLUMN1
-------
AB786GH
7890651

Or, as Wernfried commented, like operator:

<snip>
  7  select column1
  8  from test
  9  where column1 like '%78%';

COLUMN1
-------
AB786GH
7890651
0

Thank you all for your valuable Answers

It is Benn resolved by the below query :

Select * from table1 where regex_like(column_name,'[[:alpha:]]');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.