SQLite - reverse string function

Question

Is there a function for reversing string in SQLite? I'm unable to find anything in the documentation.

Thanks!

Answer 1

I solved my problem storing word reverses which I obtained via this PHP script:

class MyDB extends SQLite3
{
        function __construct()
        {
                $dbFile = __DIR__ . '/Dictionary.sqlite';
                $this->open($dbFile);
        }            
}

$db = new MyDB();

$db->createFunction('rev', 'strrev', 1);
$db->exec('UPDATE dict_en SET word_rev = rev(word)');

echo "done";

Answer 2

Hello MartyIX,

Yes, Sqlite3 has a function you can use to reverse strings.

Reverse Strings in Sqlite

Conditions

1. You have to know the length of the string.
2. If you want to reverse all strings in a column, all strings must be of equal length.

Example

Imagine you got a table named myTable like this one.

_id |   myField
--------------------
1   |   08641944
2   |   08950407
3   |   00250898
4   |   00498293
5   |   04502851
6   |   03146310
7   |   03146310
8   |   03146310
9   |   03146310

In this case the column myField contains strings of 8 characters in length.

Query

SELECT myField,
    SUBSTR(myField,-1,1)||
    SUBSTR(myField,-2,1)||
    SUBSTR(myField,-3,1)||
    SUBSTR(myField,-4,1)||
    SUBSTR(myField,-5,1)||
    SUBSTR(myField,-6,1)||
    SUBSTR(myField,-7,1)||
    SUBSTR(myField,-8,1) 'reversed' 
FROM myTable;

Result

myField     |    reversed
-------------------------
08641944    |    44914680
08950407    |    70405980
00250898    |    89805200
00498293    |    39289400
04502851    |    15820540
03146310    |    01364130
03146310    |    01364130
03146310    |    01364130
03146310    |    01364130  

Explanation

The negative integer, e.g. -1 in SUBSTR(myField,-1,1),
tells Sqlite to start at the rightmost character in the string.

The positive integer, e.g. 1 in SUBSTR(myField,-1,1),
tells Sqlite to pick just one character.

The ||, in e.g. SUBSTR(myField,-1,1)||,
invokes the group_concat(X) function.

The 'reversed', in SUBSTR(myField,-8,1) 'reversed',
tells Sqlite to put the reversed strings in a new column labeled reversed.

The SELECT statement above would normally be put inside
a CREATE VIEW AS statement.

Example

CREATE VIEW AS

CREATE VIEW reversed AS
    SELECT myField,
        SUBSTR(myField,-1,1)||
        SUBSTR(myField,-2,1)||
        SUBSTR(myField,-3,1)||
        SUBSTR(myField,-4,1)||
        SUBSTR(myField,-5,1)||
        SUBSTR(myField,-6,1)||
        SUBSTR(myField,-7,1)||
        SUBSTR(myField,-8,1) 'reversed' 
    FROM myTable;

Update

If the string is 1000 characters long, as hamstergene points out, you will have to use your scripting language to create a function that do the two following operations.

1. Query the field to get string length.
2. Create the SELECT statement programmatically based on the count.

Perhaps hamstergene could help you with that.

Answer 3

Using a common table expression it is possible to reverse a string in SQLite.

WITH reverse(i, c) AS (
    values(-1, '')
    UNION ALL SELECT i-1, substr('dlrow olleh', i, 1) AS r FROM reverse
    WHERE r!=''
) SELECT group_concat(c, '') AS reversed FROM reverse;

Returns hello world.

Answer 4

There is no builtin function for that. You can add custom function, like in this example in Python:

import sqlite3
conn = sqlite3.connect("")

conn.create_function("strrev", 1, lambda s: s[::-1])

cur = conn.cursor()
cur.execute(r''' SELECT strrev('hello, world') ''')
print(cur.fetchone()[0]) #dlrow ,olleh

Answer 5

As others have already explained, SUBSTR with a negative first parameter returns inverted text. This could be used with LENGTH to not depend on knowing the actual length of the field. So:

SELECT SUBSTR(myfield , -LENGTH (myfield), LENGTH (myfield)) as inverted_myfield FROM [...]

This will return myfield but with inverted String (inverted_myfield).