How to Implement a Substring Search in SQL

Search a substring with sql query

You could use instr to find the position of the space, and then use substr to extract the number before it. Then you could cast it to an int, and query its max:

SELECT MAX(CAST(SUBSTR(col1, 2, INSTR(col1, ' ') - 1) AS INT))
FROM table1

DBFiddle demo

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

If you need all words to be present, use this:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
  AND column1 LIKE '%word2%'
  AND column1 LIKE '%word3%'

If you want something faster, you need to look into full text search, and this is very specific for each database type.

Fastest way to find string by substring in SQL?

If you want to use less space than Randy's answer and there is considerable repetition in your data, you can create an N-Ary tree data structure where each edge is the next character and hang each string and trailing substring in your data on it.

You number the nodes in depth first order. Then you can create a table with up to 255 rows for each of your records, with the Id of your record, and the node id in your tree that matches the string or trailing substring. Then when you do a search, you find the node id that represents the string you are searching for (and all trailing substrings) and do a range search.

SQL Search Query with substring

Hard to say exactly, based on what you've written, but it should be something like this:

SELECT * FROM Items WHERE Name in ('MyProduct', 'My Stuff', 'Super Product')

By the way, I've found it LINQPad to be very useful in situations like this: you can point it at a database, write a LINQ query, and then click the "SQL" tab to see what SQL was produced for that query. The SQL tends to be overly verbose, but you can usually get a pretty good idea of what it's doing and come up with a simplified version yourself.

Update

Based on the updated question, it's more clear what you want to do. I would recommend that you create a full-text search capable field, and use FREETEXT to query it. This is exactly the sort of thing that full-text search was made for.

Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.

query for substring formation

This is one of those examples of how there's similar functionality between SQL and the various extensions, but are just different enough that you can not guarantee portability between all databases.

The SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99. Why this took them so long, I don't know.

The crux of your need is to obtain a substring of the existing column value, so you need to know what the database substring function(s) are called.

Oracle

SELECT SUBSTR('abcd_01', -2) FROM DUAL

Oracle doesn't have a RIGHT function, with is really just a wrapper for the substring function anyway. But Oracle's SUBSTR does allow you to specify a negative number in order to process the string in reverse (end towards the start).

SQL Server

Two options - SUBSTRING, and RIGHT:

SELECT SUBSTRING('abcd_01', LEN('abcd_01') - 1, 2)
SELECT RIGHT('abcd_01', 2)

For brevity, RIGHT is ideal. But for portability, SUBSTRING is a better choice...

MySQL

Like SQL Server, three options - SUBSTR, SUBSTRING, and RIGHT:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)
SELECT SUBSTRING('abcd_01', LENGTH('abcd_01') - 1, 2)
SELECT RIGHT('abcd_01', 2)

PostgreSQL

PostgreSQL only has SUBSTRING:

 SELECT SUBSTRING('abcd_01' FROM LENGTH('abcd_01')-1 for 2)

...but it does support limited pattern matching, which you can see is not supported elsewhere.

SQLite

SQLite only supports SUBSTR:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)

Conclusion

Use RIGHT if it's available, while SUBSTR/SUBSTRING would be better if there's a need to port the query to other databases so it's explicit to others what is happening and should be easier to find equivalent functionality.