Select distinct values with matching string

I have some values like: Test 1, Test 2, Test 3, Test 4, Test 5 and i want to select all value including common word itself so select * from mytable where value like ‘%Test%’ it returns me all values but i want result like Test, Test 1, Test 2, Test 3, Test 4, Test 5 so first record should be the Test itself and then original values: Test 1, Test 2, Test 3, Test 4, Test 5 How can i do that any help much appreciated?

SELECT 'Test' AS value
UNION ALL
SELECT * FROM mytable WHERE value LIKE '%Test%'
ORDER BY value

Test is just an example string i am trying to make a generic query

In what language?

So…

SELECT 'GenericValue' AS value
UNION ALL
SELECT * FROM mytable WHERE value LIKE '%GenericValue%'
ORDER BY value

… he’s GIVEN you the generic query.

Any search for terms “Test 1”, “Test 2”, etc, a list generated by looking for %Test%, would already have been found by the original query, because they contain %Test%.
adding things to your search string does not increase the scope, it decreases it.

2 Likes