CODEWITHSUNDEEP
sqlsqlite
user2431727
user2431727

Reputation: 907

Compare ASCII value in sql

Database table contains columns called 'name', 'nameInASCII'. Which have value

'SABARI DAS',
'83 65 66 65 82 73 32 68 65 83'

respectively. I want to write query for getting above row even if the input 'name' variable is in swap order.(ex : DAS SABARI). How is it possible ?

Upvotes: 1

Views: 226

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

The only simple/quick option which comes to mind would be to make a judicious use of the LIKE operator and just search for components of the name separately, e.g.

SELECT *
FROM yourTable
WHERE name LIKE '%SABARI%' AND name LIKE '%DAS%';

If this would not be specific enough, then full text search might be another option:

CREATE VIRTUAL TABLE yourVirtualTable USING FTS5(name, nameInASCII);
INSERT INTO yourVirtualTable (name, nameInASCII)
VALUES
    ('SABARI DAS', '83 65 66 65 82 73 32 68 65 83');

With full text search setup, now we can search for both a given first and last name at the same time:

SELECT *
FROM yourVirtualTable
WHERE yourVirtualTable MATCH 'SABARI AND DAS';

The results of the above query would not change were we to match DAS AND SABARI, i.e. the order of search terms does not matter.

Upvotes: 1

Related Questions