Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find First 4 words and Last 4 words from database after particular keywords?

Tags:

mysql

I want to find first 4 words and last 4 words after the particular keyword from database. Suppose the keyword is "Account" then I want to find first 4 words before "Account" appear in a line and 4 words after "Account" appear in a line.

Currently I am using this query for finding first 20 characters and last 20 characters after particular keyword.

SELECT 
SUBSTRING(line.WD, LOCATE('%Account%', line.WD) - 20, 20) AS First20Char, 
SUBSTRING(line.WD, LOCATE('%Account%', line.WD)+LENGTH('a'), 20) AS Last20Char 
FROM `line` WHERE line.WD LIKE '%Account%'
like image 909
Sanjay Singh Avatar asked Apr 09 '12 06:04

Sanjay Singh


People also ask

How do you find words that begin and end with a vowel in SQL?

To check if a name begins ends with a vowel we use the string functions to pick the first and last characters and check if they were matching with vowels using in where the condition of the query. We use the LEFT() and RIGHT() functions of the string in SQL to check the first and last characters.

How do you extract first 5 letter of a string in SQL?

The LEFT() function extracts a number of characters from a string (starting from left).

How do I get last 5 entries in SQL?

METHOD 1 : Using LIMIT clause in descending orderof specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending. Since Employee table has IDs, we will perform ORDER BY ID in our query.


1 Answers

SQL is called *S*QL for a reason -- you aren't going to be able to do something this advanced without the help of an intermediary programming language --- unless you want things to get messy.

The short answer is that you will have to use a UDF (user defined function) to first split, then parse your delimited string (on spaces ' ') then you can apply your -4/+4 logic.

Here is the best article on the net on SQL-UDFs to get you started:

http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

Good luck!

PS -- it'll be a lot easier to just grab your +/-30 chr then do the parsing externally though =]. It'll probably save more system resources too.

like image 101
Authman Apatira Avatar answered Nov 14 '22 23:11

Authman Apatira