Re: [sqlite] Substring question
It's not obvious but this works
CREATE TABLE x (s string);
INSERT INTO "x" VALUES('ab:cdef');
INSERT INTO "x" VALUES('ghij:klmn');
sqlite> select
ltrim(ltrim(s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),':') from
x;
ltri
cdef
klmn
Just make sure your char set contains all possible chars left of the :
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Max Vlasov [max.vla...@gmail.com]
Sent: Saturday, June 25, 2011 3:43 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Substring question
On Sat, Jun 25, 2011 at 9:53 AM, Pete wrote:
> I need to select a substring of a column that starts 1 character after a
> colon in the column and continues to the end of the column. For example,
> if
> the column contained "abc:xyz" I want the select statement to return only
> "xyz" (the characters after the colon). The substr function requires
> specific character positions - is there a way to do this?
>
>
There's no such function is sqlite so your currently has two options:
- if your strings have limited length you can create a statement with a case
case when substr(..., 2, 1)=':' then when substr(..., 3, 1)=':' ... end
you even can write a supplemental program when you enter the range for the
length and the position of the colon and the one gives you the case
statement
- write your own (user) function. If you didn't write any before, believe
be, it's very easy. If you go this way I recommend keeping compatibility at
least with MySql in this case (so either "position" or "locate" with the
corresponding parameters). This will give you theoretical possibility to
export you statements in the future without a change. I did this for example
when I implemented find_in_set
Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring question
If you can't have two : after each other then this should work:
SELECT LTRIM(LTRIM('abc:xyz',REPLACE('abc:xyz',':','')),':')
SELECT LTRIM(LTRIM(Field1,REPLACE(Field1,':','')),':') FROM Table1
--
View this message in context:
http://old.nabble.com/Substring-question-tp31924687p31928255.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Substring question
I need to select a substring of a column that starts 1 character after a colon in the column and continues to the end of the column. For example, if the column contained "abc:xyz" I want the select statement to return only "xyz" (the characters after the colon). The substr function requires specific character positions - is there a way to do this? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring question
On Sat, Jun 25, 2011 at 9:53 AM, Pete wrote: > I need to select a substring of a column that starts 1 character after a > colon in the column and continues to the end of the column. For example, > if > the column contained "abc:xyz" I want the select statement to return only > "xyz" (the characters after the colon). The substr function requires > specific character positions - is there a way to do this? > > There's no such function is sqlite so your currently has two options: - if your strings have limited length you can create a statement with a case case when substr(..., 2, 1)=':' then when substr(..., 3, 1)=':' ... end you even can write a supplemental program when you enter the range for the length and the position of the colon and the one gives you the case statement - write your own (user) function. If you didn't write any before, believe be, it's very easy. If you go this way I recommend keeping compatibility at least with MySql in this case (so either "position" or "locate" with the corresponding parameters). This will give you theoretical possibility to export you statements in the future without a change. I did this for example when I implemented find_in_set Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Substring question
I need to select a substring of a column that starts 1 character after a colon in the column and continues to the end of the column. For example, if the column contained "abc:xyz" I want the select statement to return only "xyz" (the characters after the colon). The substr function requires specific character positions - is there a way to do this? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
