Array of characters in LIKE predicate

[rajasekhart]

Hi,

Do we have the facility of searching a string as follows.

Eg: i want to retrieve the names of the members whose name begins with 'A' or 'B' or 'C'

For this i write the query as follows in sql.

select * from MEMBERS where NAME LIKE '[A-C]%'

Do we have the above facility in vertica too???

Thanks,
Raj!

[id10t]

Hi!

As option:

Code: Select all

select * from  members where left(name,1) in ('a','b','c');

or

Code: Select all

select * from  members where REGEXP_LIKE(name, '^[a-c]');

[rajasekhart]

Hi Daniel,

Thats perfectly meeting my requirement.

Thanks a lot,
Raj!

[nnani]

Hello Skwa,

I had a look at the specified topic and it looks good to me, but I was curious to know whether the example shown below can be modified for some different patter matching

Code: Select all

select * from  members where REGEXP_LIKE(name, '^[a-c]');

The patter matching for strings here is done where the strings start from letters ranging from a-c, In my scenario, we have different strings look for
Lets say abc%, def%, fgh%, %mno.

Can we modify the above sql for this

[id10t]

Hi!

Code: Select all

sampler=> select * from nnani ;
 id |  name  
----+--------
  1 | abc
  2 | abcxxx
  3 | xxxabc
  4 | def
  5 | defyyy
  6 | zzzdef
  7 | fgh
  8 | fgh---
  9 | ooofgh
 10 | mno
 11 | mnogo
 12 | gomno
(12 rows)

Code: Select all

sampler=> select * from nnani where regexp_like(name, '^((abc)|(def)|(fgh))|(mno)$');
 id |  name  
----+--------
  1 | abc
  2 | abcxxx
  4 | def
  5 | defyyy
  7 | fgh
  8 | fgh---
 10 | mno
 12 | gomno
(8 rows)

[nnani]

Can we use this in a case statement.

If yes. Can you give me an example.

I tried it at my end, but it says

ERROR 4286: Operator does not exist: varchar = boolean
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts

[id10t]

Hi!

Can you give me your "wrong" query?