Thread: automatically lower string and remove unnecessary whitespace when INSERT INTO table
automatically lower string and remove unnecessary whitespace when INSERT INTO table
From
Akbar
Date:
Hi, I have table which has two column ( column a & b ). One of the column has type string..... that is column_a. Assume somebody insert this value to my table: INSERT INTO my_table ( column_a ) VALUES ( ' Bla bla bla ' ); I want that string value ( ' Bla bla bla ' ) to be lowered before insert into my table and remove unnecessary space. So the value will be 'bla bla bla'. The space is just one. There will be no space in front and back of the string. I don't want double space between word too. How do I accomplish that????? This is my best effort: CREATE RULE string_insert AS ON INSERT TO my_table DO UPDATE my_table SET column_a = lower(column_a); However this way is not efficient because it will update all row. I just want to update only row which has just been inserted into table. And how do you remove the double space between words? I know there is function trim to remove leading and trailing space. But space more than one between two words????? Thank you. Regards, Akbar
Hi Akbar, What you are looking are trigers. CREATE TRIGGER <triger_name> BEFORE INSERT OR UPDATE ON <table_name> FOR EACH ROW EXECUTE PROCEDURE <function_name>(<function params>); To find out how to write functions, which can be used in triger, read the documentation. Kaloyan Akbar wrote: > Hi, I have table which has two column ( column a & b ). One of the > column has type string..... that is column_a. > Assume somebody insert this value to my table: > INSERT INTO my_table ( column_a ) VALUES ( ' Bla bla bla ' ); > > I want that string value ( ' Bla bla bla ' ) to be lowered > before insert into my table and remove unnecessary space. So the value > will be 'bla bla bla'. The space is just one. There will be no space > in front and back of the string. I don't want double space between > word too. How do I accomplish that????? > > This is my best effort: > CREATE RULE string_insert AS ON INSERT TO my_table > DO UPDATE my_table SET column_a = > lower(column_a); > > However this way is not efficient because it will update all row. I > just want to update only row which has just been inserted into table. > And how do you remove the double space between words? I know there is > function trim to remove leading and trailing space. But space more > than one between two words????? > > Thank you. > > Regards, > > Akbar > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > >