Searching and matching keyword

[yigit darcin]

Hello All,

I am using Postgres Db and I have this following problem:

My requirement is getting all the results based on the keyword but, the results should be ascending based on matching the keyword

For example I have this Number_Prefix table:

id prefix priority
1 90212 1
2 90212 2
3 90216 1
4 9021 1
5 9021 2
6 90 1

when I make a select with keyword = '90212' on prefix column, what I want to see is :

1 90212 1
2 90212 2
4 9021 1
5 9021 2
6 90 1

or I make a select with keyword = '9021' on prefix column, what I want to see is :

4 9021 1
5 9021 2
6 90 1

so the query will look for 9021 then 902 and 90 and so on.

can this be done in one single sql call?

thanks in advance.

Yigit

[Sagar Rohankar]

Not sure, but try.

SELECT * FROM  Number_Prefix WHERE prefix = '9021' OR prefix = '902' OR prefix = '90''

[Jeanne Boyarsky]

Yigit,
The order part is easy "order by keyword desc, priority".

For the rest of it, I think you'd need a stored procedure to do it in one call - looping by substring.