Any idea for a single UPDATE statement to drop ALL begining letters, leading zeros and leading spaces from the CODE Thanks! Did not have the time to check, but I suspect it might fail for certain codes create table TT (id int not null primary key, code varchar(20) not null); insert into TT (id, code) values (1, 'XD23414'), (2, 'LKJ454678'), (4, '0023821'), (5, '076994'), (6, 'AG00086904'), (7, '834292'), (8, 'PERTM98'), (9, 'M7521315'), (10, 'M 21315'), (11, '8479 '); with prefix (n) as ( values ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),(' I'), ('J'), ('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),(' S'), ('T'), ('U'),('V'),('W'),('X'),('Y'),('Z'),('0'),(' ') ), suspects (id, n, code) as ( select id, locate(n, code), code from prefix, TT where locate(n, code) 0 ), position(id, n) as ( select id, max(n) from suspects s1 where substr(code, n 1) not in ( select n from prefix ) group by id ), action (id, new_code) as ( select * from new table ( update TT set code = substr(code, (select n 1 from position where id = TT.id) ) where id in (select id from position) ) X ) select * from action; select * from TT" ID CODE ----------- -------------------- 1 23414 2 454678 4 023821 5 76994 6 0086904 7 834292 8 98 9 7521315 10 21315 11 8479 10 record(s) selected.
I have an existing database and I make some changes in Sql Server Management Studio, how can I adapt those changes in the Visual Basic project. Net Rate Topic A Really Simple Database Create a Database using Access & Express 2008 Hi Modi, I would prefer to update directly from VB, then I could send an update to my installed aplication, BUT how do you update the data or table names from vb, or do you know of any good references I can read I cant see anything on google, I may be searching wrong. Honestly having the app call a stored procedure makes updates easier..
As an example if I change a table name in Sql Server Management Studio how can I update that change in my VB project. set the connection and the sql statement string to the command.... since if it has the same procedure name, input, and output the guts don't matter to it.
ID CODE ------------- 1 XD23414 2 LKJ454678 4 0023821 5 076994 6 AG00086904 7 834292 8 PERTM98 9 M7521315 10 M 21315 11 8479 Need to come up with update sql to drop the begining letters, leading zeros and leading spaces from the CODE so, after the update, the table looks like this: ID CODE ------------- 1 23414 2 454678 4 23821 5 76994 6 86904 7 834292 8 98 9 7521315 10 21315 11 8479 I tried substr() function to do update, but could not do all update in single execution: update CODE_TABLE set CODE = ltrim(substr(CODE,2,length(rtrim(CODE)))) where CODE is not null AND substr(CODE, 1, 1) in ('A','B','C','D','E','F','G','H','I','J','K','L',' M','N','O','P','Q','R','S','T','U','V','W','X','Y' ,'Z','0', ' '); I cannot go for stroed procedure to loop through until length(CODE).
Any idea for a single UPDATE statement to drop ALL begining letters, leading zeros and leading spaces from the CODE Thanks!
If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.