Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 1:06pm, venkat easwar wrote:

> As far as I am aware trim will remove the characters only in ends, not in the 
> middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I 
> wrong 
> somewhere. Guess NO.

Venkat, you are quite right.  Sorry about that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Harish,

Consider doing things outside sqlite. Definitely you should be using C API or 
php API or some other for making query to DB. Why don't you think of doing 
things outside sqlite
 VENKAT
Bug the Bugs




From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:08:29 PM
Subject: Re: [sqlite] Trigger to filter out characters


Simon,

Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
'9' and a few more characters. 
180+ characters need to be removed (filtered out).
So I will need to call Trim() so many times.
Since we cannot write loops I wonder how I can call it many times.

-Harish

Simon Slavin-3 wrote:
> 
> 
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function
> from
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> helpful.  You'll have to list all characters  you /don't/ want left in the
> string.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863194.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Simon,

As far as I am aware trim will remove the characters only in ends, not in the 
middle. Assume an input like "12venkatpw", this cannot be trimmed. Or am I 
wrong 
somewhere. Guess NO.

Thanks
 VENKAT
Bug the Bugs.




From: Simon Slavin <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 6:19:04 PM
Subject: Re: [sqlite] Trigger to filter out characters


On 7 Feb 2011, at 12:38pm, Harish CS wrote:

> Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
> '9' and a few more characters. 
> 180+ characters need to be removed (filtered out).
> So I will need to call Trim() so many times.
> Since we cannot write loops I wonder how I can call it many times.

No, you just call it once and list all those characters in the second parameter:

UPDATE contacts SET phoneNumber to TRIM(rawPhoneNumber, 'abcdefghi ... 
xyz!@£$%^&()_-={}[]:"|;''\<>?,./')

Note that as above to put an apostrophe inside an SQLite string you double-it.

Simon.
___
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] Trigger to filter out characters

[Igor Tandetnik]

Simon Slavin  wrote:
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function

trim just removes leading and trailing characters. It won't help with, say, 
'1x2'. The closest you could come using built-in functions is something like

replace(replace(...(replace(new.A, 'a', ''), 'b', ''), 'c', '')...)

but that's impractical.

Personally, I'd do the transformation in the application code. Barring that, a 
custom function (like strfilter mentioned in this thread) is in order.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 12:38pm, Harish CS wrote:

> Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
> '9' and a few more characters. 
> 180+ characters need to be removed (filtered out).
> So I will need to call Trim() so many times.
> Since we cannot write loops I wonder how I can call it many times.

No, you just call it once and list all those characters in the second parameter:

UPDATE contacts SET phoneNumber to TRIM(rawPhoneNumber, 'abcdefghi ... 
xyz!@£$%^&()_-={}[]:"|;''\<>?,./')

Note that as above to put an apostrophe inside an SQLite string you double-it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Harish, neither trigger nor trim will be useful for you directly as such. I 
suggest writing a custom sqlite function to perform the operation. You try 
writing custom functions, if I figure out any idea, I will get back to you.

http://www.sqlite.org/c3ref/create_function.html

VENKAT
Bug the Bugs




From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 6:01:31 PM
Subject: Re: [sqlite] Trigger to filter out characters


Venkat,

Thanks. 
But this is not filtering characters.
For example, if user enters '12ab34', it should remove 'ab' and enter
'1234'.

Thanks,
Harish


venkat easwar wrote:
> 
> Well. I thought it should be an easy deal for you hence left that part.
> Now 
> giving the conditioned trigger assuming the following condition. I leave
> the 
> testing part to your concern.
> 
>>I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w'
> 
> create trigger if not exists  after insert on  when 
> new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
> beginupdate  B=new.A;
> end;
> 
> Only if the values inside the braces are inserted it will be copied to
> column B.
> 
> 
> VENKAT
> 
> Bug the Bugs
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:59:50 PM
> Subject: Re: [sqlite] Trigger to filter out characters
> 
> 
> Hi Venkat,
> 
> Could you please show me how to write the condition (to filter out
> characters)?
> 
> Thanks,
> Harish
> 
> 
> 
> venkat easwar wrote:
>> 
>> Hi Harish,
>> 
>> Yes it is possible. Look below for solution.
>> 
>> create trigger if not exists  after insert on 
>> when 
>> new.A=
>> begin
>> update  B=new.A;
>> end;
>> 
>> 
>> There is nothing tricky or hard for inserting into another table, same
>> things 
>> should go.
>> 
>> VENKAT
>> Bug the Bugs
>> 
>> 
>> 
>> 
>> 
>> From: Harish CS <cshar...@gmail.com>
>> To: sqlite-users@sqlite.org
>> Sent: Mon, February 7, 2011 2:08:16 PM
>> Subject: [sqlite]  Trigger to filter out characters
>> 
>> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
>> 
>> Thanks
>> -Harish
>> 
>> -- 
>> View this message in context: 
>> 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863148.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[Jean-Christophe Deschamps]

>Could you please show me how to write the condition (to filter out
>characters)?

Use the strfilter function found in extension-functions.c downloadable 
from http://www.sqlite.org/contrib/

Once built and loaded, you can use a trigger similar to

create trigger if not exists trFilterAB after insert on mytable
begin
update mytable set columnB = strfilter(new.columnA, '0123456789*#+pw') 
where rowid = new.rowid;
end;




@venkat,

I guess the OP needs to filter out characters not in the prescribed 
set, not sort out if the data is a 1-character string in this set. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Simon,

Thanks. Trim(X,Y) is useful but I need to remove anything other than '0' - '
'9' and a few more characters. 
180+ characters need to be removed (filtered out).
So I will need to call Trim() so many times.
Since we cannot write loops I wonder how I can call it many times.

-Harish

Simon Slavin-3 wrote:
> 
> 
> On 7 Feb 2011, at 8:38am, Harish CS wrote:
> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
> 
> I don't think you need a trigger.  You might find the 'trim(X,Y)' function
> from
> 
> http://www.sqlite.org/lang_corefunc.html
> 
> helpful.  You'll have to list all characters  you /don't/ want left in the
> string.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863194.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

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Venkat,

Thanks. 
But this is not filtering characters.
For example, if user enters '12ab34', it should remove 'ab' and enter
'1234'.

Thanks,
Harish


venkat easwar wrote:
> 
> Well. I thought it should be an easy deal for you hence left that part.
> Now 
> giving the conditioned trigger assuming the following condition. I leave
> the 
> testing part to your concern.
> 
>>I need to take out the value of A, filter out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w'
> 
> create trigger if not exists  after insert on  when 
> new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
> beginupdate  B=new.A;
> end;
> 
> Only if the values inside the braces are inserted it will be copied to
> column B.
> 
> 
> VENKAT
> 
> Bug the Bugs
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:59:50 PM
> Subject: Re: [sqlite] Trigger to filter out characters
> 
> 
> Hi Venkat,
> 
> Could you please show me how to write the condition (to filter out
> characters)?
> 
> Thanks,
> Harish
> 
> 
> 
> venkat easwar wrote:
>> 
>> Hi Harish,
>> 
>> Yes it is possible. Look below for solution.
>> 
>> create trigger if not exists  after insert on 
>> when 
>> new.A=
>> begin
>> update  B=new.A;
>> end;
>> 
>> 
>> There is nothing tricky or hard for inserting into another table, same
>> things 
>> should go.
>> 
>> VENKAT
>> Bug the Bugs
>> 
>> 
>> 
>> 
>> 
>> From: Harish CS <cshar...@gmail.com>
>> To: sqlite-users@sqlite.org
>> Sent: Mon, February 7, 2011 2:08:16 PM
>> Subject: [sqlite]  Trigger to filter out characters
>> 
>> 
>> Table T has two varchar columns A and B. UI allows entering values into
>> column A only.
>> On insert/update of records, I need to take out the value of A, filter
>> out
>> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and
>> copy
>> it to column B. Is it possible to write a trigger to achieve this? If yes
>> please give me a sample. (Also, if column B is in another table, is it
>> possible?)
>> 
>> Thanks
>> -Harish
>> 
>> -- 
>> View this message in context: 
>> 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30863148.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

Re: [sqlite] Trigger to filter out characters

[Simon Slavin]

On 7 Feb 2011, at 8:38am, Harish CS wrote:

> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)

I don't think you need a trigger.  You might find the 'trim(X,Y)' function from

http://www.sqlite.org/lang_corefunc.html

helpful.  You'll have to list all characters  you /don't/ want left in the 
string.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Well. This is the one I did not expect. Update without condition is updating 
all 
the rows. Hence make it with conditioned update :) :)

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
begin
update  set B=new.A where A=new.A;
end;
 
If you find more bugs, well it is good, we will get them bugged.
VENKAT
Bug the Bugs




From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 5:19:51 PM
Subject: Re: [sqlite] Trigger to filter out characters

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs




From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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-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] Trigger to filter out characters

[venkat easwar]

OOPS.. a mistake.. find the corrected things below.

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  set B=new.A;
end;

Missed set in my last mail. It would have given you a syntax error.
VENKAT
Bug the Bugs




From: venkat easwar <hareas...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, February 7, 2011 4:41:19 PM
Subject: Re: [sqlite] Trigger to filter out characters


Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this  message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Well. I thought it should be an easy deal for you hence left that part. Now 
giving the conditioned trigger assuming the following condition. I leave the 
testing part to your concern.

>I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w'

create trigger if not exists  after insert on  when 
new.A IN ('0','1','2','3','4','5','6','7','8','9','*','#','+','p','w')
beginupdate  B=new.A;
end;

Only if the values inside the braces are inserted it will be copied to column B.


VENKAT

Bug the Bugs



From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:59:50 PM
Subject: Re: [sqlite] Trigger to filter out characters


Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger to filter out characters

[Harish CS]

Hi Venkat,

Could you please show me how to write the condition (to filter out
characters)?

Thanks,
Harish



venkat easwar wrote:
> 
> Hi Harish,
> 
> Yes it is possible. Look below for solution.
> 
> create trigger if not exists  after insert on  when 
> new.A=
> begin
> update  B=new.A;
> end;
> 
> 
> There is nothing tricky or hard for inserting into another table, same
> things 
> should go.
> 
> VENKAT
> Bug the Bugs
> 
> 
> 
> 
> 
> From: Harish CS <cshar...@gmail.com>
> To: sqlite-users@sqlite.org
> Sent: Mon, February 7, 2011 2:08:16 PM
> Subject: [sqlite]  Trigger to filter out characters
> 
> 
> Table T has two varchar columns A and B. UI allows entering values into
> column A only.
> On insert/update of records, I need to take out the value of A, filter out
> anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
> it to column B. Is it possible to write a trigger to achieve this? If yes
> please give me a sample. (Also, if column B is in another table, is it
> possible?)
> 
> Thanks
> -Harish
> 
> -- 
> View this message in context: 
> http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861878.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

Re: [sqlite] Trigger to filter out characters

[venkat easwar]

Hi Harish,

Yes it is possible. Look below for solution.

create trigger if not exists  after insert on  when 
new.A=
begin
update  B=new.A;
end;


There is nothing tricky or hard for inserting into another table, same things 
should go.

VENKAT
Bug the Bugs





From: Harish CS <cshar...@gmail.com>
To: sqlite-users@sqlite.org
Sent: Mon, February 7, 2011 2:08:16 PM
Subject: [sqlite]  Trigger to filter out characters


Table T has two varchar columns A and B. UI allows entering values into
column A only.
On insert/update of records, I need to take out the value of A, filter out
anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
it to column B. Is it possible to write a trigger to achieve this? If yes
please give me a sample. (Also, if column B is in another table, is it
possible?)

Thanks
-Harish

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger to filter out characters

[Harish CS]

Table T has two varchar columns A and B. UI allows entering values into
column A only.
On insert/update of records, I need to take out the value of A, filter out
anything other than '0' to '9', '*', '#', '+' 'p', 'w' characters and copy
it to column B. Is it possible to write a trigger to achieve this? If yes
please give me a sample. (Also, if column B is in another table, is it
possible?)

Thanks
-Harish

-- 
View this message in context: 
http://old.nabble.com/Trigger-to-filter-out-characters-tp30861522p30861522.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