Reverse matching

[xenophobia]

I have a mysql table with 2 fields:

id  |  prefix

The value will be something like:

1    |  T20

2    |  T22

3    |  T30

 

So when user key-in a value in the frontend: T20535664. I want it able to return me "1", since it is matching the first row of prefix field. How do I do that? I do not wish to use php's regex to retrieve all the rows and compare it one by one. Is SQL able to do that with the wildcard '%' ??

[onedumbcoder]

$key = 'T20'

SELECT id FROM table WHERE prefix LIKE '$key%';

[xenophobia]

...........

The other way round.....

 

$key = 'T20'

SELECT id FROM table WHERE '$key%' LIKE prefix;

[gizmola]

Onedumbcoder gave you the sql syntax which works fine, and will be optimized so long as the prefix column has an index on it.

[xenophobia]

Ermm sorry if my question is confusing. let me give you example:

 

$user_input = "T20535664";

// Something similiar
$sql = "SELECT id FROM table WHERE prefix LIKE '$user_input%'";

 

of course the sql above will never return '1' since it is not matching any of the prefix.

 

In regex:

("T20535664").match(/^prefix/);

 

This is what I want. But I have to retrieve all the prefix row by row before i can perform the searching.

 

Got what I mean?

[gizmola]

Yes, sorry I missed out on what you were saying.  Is there a rule that governs the size of the prefix? 

[xenophobia]

Ermmm first three digits should be enough to determine the ID.... But Im just afraid that going to increase...

 

So meaning in SQL we can achieve this?

[Daniel0]

If the prefix is always three characters long, then just truncate the user input to three characters.

[xenophobia]

Ermmm first three digits should be enough to determine the ID.... But Im just afraid that going to increase...

 

So meaning in SQL we can achieve this?

 

Sorry, is "can't".

 

If the prefix is always three characters long, then just truncate the user input to three characters.

 

Nvm, is okay, I just wanna know whether there is a way in SQL. Seems like it doesn't.

 

Thanks you all.

[Daniel0]

Well there is:

 

SELECT id FROM table WHERE prefix = SUBSTRING('$prefix', 1, 3);