RLIKE in SQL

[pankajit09]

I am firing the below query for extracting were+10 from the database but its not working. Please correct it.

SELECT parameter FROM table WHERE parameter RLIKE '\\bwere\\b'

[pankajit09]

RLIKE in SQL

I am firing the below query for extracting were+10 from the database but its not working. Please correct it.

SELECT parameter FROM table WHERE parameter RLIKE '\\bwere\\b'

[Atli]

Why are you using the backspace char (\b)?
Have you tried just using 'where'?

[dlite922]

I am firing the below query for extracting were+10 from the database but its not working. Please correct it.

SELECT parameter FROM table WHERE parameter RLIKE '\\bwere\\b'

I'm pretty sure you've got the regex wrong.

What do you mean were+10? Everything that starts with the word lowercase were and up to 10 characters there after?

Might help you better if you post this in the SQL forum also.

[pankajit09]

Why are you using the backspace char (\b)?
Have you tried just using 'where'?

The string can be like "were+10"

I want an exact match of "were".

[pankajit09]

I'm pretty sure you've got the regex wrong.

What do you mean were+10? Everything that starts with the word lowercase were and up to 10 characters there after?

Might help you better if you post this in the SQL forum also.

"were+10" is just a string.

I want to find the exact occurence of "were".

[Atli]

If you want an exact match, you should use the LIKE syntax rather than RLIKE.
[code=sql]
SELECT cols FROM tbl WHERE searchCol LIKE 'where';
[/code]
If you want all rows starring with 'where', add the wild-card char (%) after the keyword:
[code=sql]
SELECT cols FROM tbl WHERE searchCol LIKE 'where%';
[/code]
You can add a wild-card anywhere in the stringt. Be careful tho. It's very easy to over-use.

[Atli]

As this is in no way a PHP problem, I have merged the duplicate thread you posted in the PHP forums with this one.

Please do not to double post your questions, and try to keep them in the appropriate forums.

Moderator

[pankajit09]

If you want an exact match, you should use the LIKE syntax rather than RLIKE.
[code=sql]
SELECT cols FROM tbl WHERE searchCol LIKE 'where';
[/code]
If you want all rows starring with 'where', add the wild-card char (%) after the keyword:
[code=sql]
SELECT cols FROM tbl WHERE searchCol LIKE 'where%';
[/code]
You can add a wild-card anywhere in the stringt. Be careful tho. It's very easy to over-use.

Will it work for substrings also ?

For eg: We have a string "11+firstwere+1 2"

If we use LIKE then it will return true for the string . It should return false.

[r035198x]

Will it work for substrings also ?

For eg: We have a string "11+firstwere+1 2"

If we use LIKE then it will return true for the string . It should return false.

if you put like '%where%', rows with "11+firstwere+1 2" will not be returned.

[pankajit09]

if you put like '%where%', rows with "11+firstwere+1 2" will not be returned.

No it returns.

I tested it.

[r035198x]

No it returns.

I tested it.

Are you sure the field has "11+firstwere+1 2" and not "11+firstwhere+ 12"?

[dlite922]

No it returns.

I tested it.

Okay, let's end this thread:

1. If you want the EXACT phrase "were" and nothing else, use an equal sign: fieldName = "were".

2. If you want phrases that contain the word "were" use wild cards (the percentage sign %) appropriatly. If you put "%were" it will return anything that ENDs with "were", if you put "were%" it will return anything that STARTS with "were". If you put "%were%" it will return ANY string that contains where, regardless of position.

Good luck,


Dan

[pankajit09]

If you put "%were%" it will return ANY string that contains where, regardless of position.

In the above case it will also return the strings in which "were" is a substring.
I don't want that.