How to get the position of a character in SQLite from the right (or the last searched character from the left) with the function instr()?

Asked Jan 03 '17 at 11:47

Active Jan 03 '17 at 11:57

Viewed 1,562 times

I try to get the last position of a character with a query with instr, but impossible to do it as the reverse() function doesn't exist in SQLite. Do you have an idea ?

SELECT substr(ZSTREETNAME, instr(ZSTREETNAME,',')+1) FROM ZTAB_PARTICULIER WHERE ZSTREETNAME like '%,%'

In the string : "Rue, test, exemple, à l'Eau, 46"
What I search : The position of the last character "," of the string
What I want : 28

edited Jan 03 '17 at 11:57

asked Jan 03 '17 at 11:47

ΩlostA

2,501
5
27
63

1

Perhaps show a sample value and desired output. – Alex K. Jan 03 '17 at 11:52
I just put what I would like in the end of the message right now. Thanks in advance :) – ΩlostA Jan 03 '17 at 11:58
Presumably the number of `,` is variable? – Alex K. Jan 03 '17 at 11:59
Yep exactly. I have a database with 2 000 000 lines, and I need to know for each lines the position of the last "," – ΩlostA Jan 03 '17 at 12:00
2

The replace/trim answer [Here](http://stackoverflow.com/questions/21388820/how-to-get-the-last-index-of-a-substring-in-sqlite) will allow you to pull off the last token (46) – Alex K. Jan 03 '17 at 12:13
1

Perfect, it works! Thank you very much. – ΩlostA Jan 03 '17 at 14:14

How to get the position of a character in SQLite from the right (or the last searched character from the left) with the function instr()?

0 Answers0