CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Raneez Ahmed
Raneez Ahmed

Reputation: 3828

Sqlite query to get list of next available characters after the matching search keyword

How to query sqlite database to get list of next available characters after the search keyword in a string field. for example, if the search keyword is ’and’

and let the strings be a list of names like :

  1. Andy Xyz
  2. Andrew Xyz
  3. Xyz Andon
  4. Xyz Miranda

then i should get the characters [y,r,o,a].

I tried the query with substr(names,1,1), but substr needs to specify the start index which will be different in each strings. Is it possible to fetch these characters using sqlite query?

Upvotes: 1

Views: 176

Answers (2)

MikeT
MikeT

Reputation: 57103

You could utilise the instr function to ascertain the start of the substr e.g. :-

SELECT *, substr(names,instr(lower(names),'and') + length('and'),1) AS onechar
FROM mytable WHERE names LIKE ('%and%');

A working example :-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (names TEXT);
INSERT INTO mytable VALUES('Andy Xyz'),('Andrew Xyz'),
('Xyz Andon'),('Xyz Miranda');
SELECT *, substr(names,instr(lower(names),'and') + length('and'),1) AS onechar FROM mytable WHERE names LIKE ('%and%');

This results in :-

enter image description here

as per your expected results.

Upvotes: 1

forpas
forpas

Reputation: 164214

You need substr() and instr(): 

select 
substr(names, instr(lower(names), lower('and')) + length('and'), 1) nextchar 
from tablename
where 
  names like '%' || 'and' || '_%'

See the demo
You can replace 'and' with any string you wish in the above query.

Upvotes: 1

Related Questions