CODEWITHSUNDEEP
databasesqlite
Vladimir Stazhilov
Vladimir Stazhilov

Reputation: 1954

How to get substring in SQLIte?

I retrieve quite a lot of data from SQLite database. When retrieving I map it to different views in my application. There is a text field in my table from which I don't want to get the full text, just first n chars. So if my query for example is:

Select description from articles where id='29';

Then how do I get the substring from description? thanks

Upvotes: 49

Views: 60390

Answers (3)

Dan D.
Dan D.

Reputation: 74645

Use the substr function.

From the list of core functions:

substr(X,Y,Z) substr(X,Y) substring(X,Y,Z) substring(X,Y)

The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

substring() is an alias for substr() beginning with SQLite version 3.34.

Upvotes: 75

Enzokie
Enzokie

Reputation: 7415

To get the substring in SQLite

You can use the builtin function in SQLite which is substr(X,Y,Z). The x field represents the string input to be sliced, the y field represents the starting point using an index, and the z field represents the substring length.

===============================
|Database Table : **articles**|
===============================
|id | description             |
-------------------------------
|29 | Lorem ipsum domit       |
===============================

Now we will try to make a select query for our description

SELECT substr(description,1,4) FROM articles where id='29';

Output would be: Lore instead of Lorem ipsum domit

Upvotes: 22

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

To extend existing answers, starting from SQLite 3.34.0:

The substr() SQL function can now also be called "substring()" for compatibility with SQL Server.

substring(X,Y) "substring()" is an alias for "substr()" beginning with SQLite version 3.34.

Upvotes: 1

Related Questions