Retrieving the closest matching string from database containing strings;

Question

I have a database where there is a column "Fruits" of type TEXT; for example if i have two strings

1. Apple %d Mango %d Banana %d Guava %d Peach %d
2. Grapes %d Plum %d Pomegranate %d Apple %d

I want to return string 1 first and then string 2. If i my Query string is

Apple 4 Mango 6 Banana 7 Guava 8 Peach 2

I can remove the %d's from the stored strings if required but in the query string the numbers will be present; Any idea how can i do this?

You want to look into the full text options that these databases offer. — Gordon Linoff, Sep 10 '13 at 10:52
@user2764478 I am not very clear on what you are asking for either. What is "Apple %d Mango %d Banana %d Guava %d Peach %d", is this all text within one field in the database for a single record?. Please can you also explain what you mean by "If my query string is Apple 4 Mango 6 Banana 7 Guava 8 Peach 2"? — ChrisProsser, Sep 10 '13 at 12:04

score 0 · Answer 1 · answered Sep 10 '13 at 12:11

You said you can remove %d's from the string, to remove numbers from query string you can use REPLACE function.

@str is the query string.

REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (@str, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')

This would replace all the numbers and then you can query further for your logic.

Retrieving the closest matching string from database containing strings;

1 Answers1