CODEWITHSUNDEEP
sqlite
John Smith
John Smith

Reputation: 12807

Number of lines in SQLite string

I have an SQLite table where a column is of type 'text'. Each item in that column is program code. I want to find out how many lines each text item is. i.e. The following should be 3 lines:

program "help"
print "Help"
return.

Upvotes: 2

Views: 293

Answers (2)

dan04
dan04

Reputation: 91207

You can count the \ns by doing

LENGTH(TheColumn) - LENGTH(REPLACE(TheColumn, X'0A', '')) + 1

Upvotes: 1

Michael Low
Michael Low

Reputation: 24506

I don't think it's possible to do that only within SQLite as is. SQLite allows for user defined functions though, how that works depends on what programming language you're using.

Otherwise you could have a separate column that stores the line count, and update that with whichever language you're using when doing inserts / updates.

Upvotes: 0

Related Questions