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!
Array of characters in LIKE predicate
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Array of characters in LIKE predicate
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Array of characters in LIKE predicate
Hi!
As option:
or
As option:
Code: Select all
select * from members where left(name,1) in ('a','b','c');
Code: Select all
select * from members where REGEXP_LIKE(name, '^[a-c]');
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Array of characters in LIKE predicate
Hi Daniel,
Thats perfectly meeting my requirement.
Thanks a lot,
Raj!
Thats perfectly meeting my requirement.
Thanks a lot,
Raj!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Array of characters in LIKE predicate
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
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
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]');
Lets say abc%, def%, fgh%, %mno.
Can we modify the above sql for this
Re: Array of characters in LIKE predicate
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)
Re: Array of characters in LIKE predicate
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
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
Re: Array of characters in LIKE predicate
Hi!
Can you give me your "wrong" query?
Can you give me your "wrong" query?