By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

UPDATE sql

P: n/a
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!

Nov 14 '07 #1
Share this Question
Share on Google+
14 Replies


P: n/a
an*************@gmail.com wrote:
<snip>
Any idea for a single UPDATE statement to drop ALL begining letters,
leading zeros and leading spaces from the CODE
Write a user defined function to return the position of the digit in the
string, then use that UDF. But since you said no stored procedures, I guess
UDFs are out of the question too.

--
Venlig hilsen /Best regards
Kristian Damm Jensen
Nov 14 '07 #2

P: n/a
On Nov 14, 8:33 am, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
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!

Hi, Anne

I'm afraid I don't have my copy on-hand, but Anthony Molinaro's _SQL
Cookbook_ (O'Reilly) has a detailed discussion of exactly the problem
you're facing (essentially, trying to extract just the numbers from an
alphanumeric string). If it helps, the solution involves transaliting
(TRANSLATE()) all tokens that aren't 0-9 with a single symbol ('@', I
believe) and then using REPLACE(). I don't suppose you have access to
http://safari.oreilly.com?

--Jeff

Nov 14 '07 #3

P: n/a
Any idea for a single UPDATE statement to drop ALL begining letters,
leading zeros and leading spaces from the CODE
You can use TRANSLATE to map A-Z to an empty string.
Then you can use STRIP/LTRIM (DB2 9 for LUW)to remove the leading spaces
and zeros'

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 14 '07 #4

P: n/a
On Nov 14, 5:33 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
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!

probably very inefficient, but it is one stmt :-). 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.

/Lennart

Nov 14 '07 #5

P: n/a
On Nov 14, 9:40 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
[...]
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.
Ooops, apparently something is wrong since there is a couple of codes
starting with 0. Dont have time to look into it though, sorry

/Lennart

Nov 14 '07 #6

P: n/a
On Nov 14, 12:51 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Nov 14, 9:40 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
[...]
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.

Ooops, apparently something is wrong since there is a couple of codes
starting with 0. Dont have time to look into it though, sorry

/Lennart

Here's a snippet, adapted from Molinaro's book. Hope it helps get you
there....:

WITH
T(DATA)
AS
(
VALUES
('SMITH800'),
('ALLEN1600'),
('WARD1250'),
('JONES2975')
)
SELECT
REPLACE(TRANSLATE(DATA,'0000000000','0123456789'), '0','') ENAME,
CAST(REPLACE(TRANSLATE(LOWER(DATA),REPEAT('Z',26),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'Z','') AS INTEGER) SAL
FROM
T;

Gives:

SMITH 800
ALLEN 1600
WARD 1250
JONES 2975

--Jeff

Nov 14 '07 #7

P: n/a
On Nov 14, 12:51 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Nov 14, 9:40 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
[...]
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.

Ooops, apparently something is wrong since there is a couple of codes
starting with 0. Dont have time to look into it though, sorry

/Lennart

Anne:

OK--here's a snippet, adapted from Molinaro's book, that I think will
point you in the right direction:

WITH
T(DATA)
AS
(
VALUES
('SMITH800'),
('ALLEN1600'),
('WARD1250'),
('JONES2975')
)
SELECT
REPLACE(TRANSLATE(DATA,'0000000000','0123456789'), '0','') ENAME,
CAST(REPLACE(TRANSLATE(LOWER(DATA),REPEAT('z',26),
'abcdefghijklmnopqrstuvwxyz'),'z','') AS INTEGER) SAL
FROM
T

GIVES:

SMITH 800
ALLEN 1600
WARD 1250
JONES 2975

HTH,

--Jeff

Nov 14 '07 #8

P: n/a
On Nov 14, 11:43 pm, deangc <dean.cochr...@gmail.comwrote:
[...]
>
I couldn't get it to work inside a MERGE. Maybe one of the gurus in
here (Serge, Lennart, Knut?)
I'll keep this one for future use ;-) Unless mistaken you cant use a
CTE in the using clause of a merge. I tried the following on V8.2 as
well as V9 :

create table T (a int not null primary key, b char(1) default 'X' not
null);
insert into T (a) values (1),(2);

db2 "merge into T using ( values 1 ) U (a) on U.a = T.a when matched
then update set B = 'Y' when not matched then insert values (U.a,
'X')";
DB20000I The SQL command completed successfully.

db2 "merge into T using ( with gen(n) as (values 1) select n from
gen ) U (a) on U.a = T.a when matched then update set B = 'Y' when not
matched then insert values (U.a, 'X')"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "using ( with
gen(n)".
Expected tokens may include: "JOIN". SQLSTATE=42601
/Lennart
Nov 15 '07 #9

P: n/a
On Nov 14, 9:51 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
[...]
>
Ooops, apparently something is wrong since there is a couple of codes
starting with 0. Dont have time to look into it though, sorry
Grmpf, it wont work when the same letter is repeated ( say
'LLJ454678' ). Anyhow, using replace as proposed by others in this
thread is clearly much better (and not only because of this )

/Lennart
Nov 15 '07 #10

P: n/a
Hello!

Why not export the table to a file, and then use any file oriented language,
like Cobol, C or perl, to do the job.
Then, import or load the file again , either into a worktable and from there
do an update, or if feasible, into the original table.

/dg
Nov 15 '07 #11

P: n/a
I couldn't get it to work inside a MERGE. Maybe one of the gurus in
here (Serge, Lennart, Knut?)

I'll keep this one for future use ;-) Unless mistaken you cant use a
CTE in the using clause of a merge. I tried the following on V8.2 as
well as V9 :
I just realized something: if they (or you) have a table of integers,
which some systems do, then you can make this work in a MERGE because
you won't need the CTE.
create table T (a int not null primary key, b char(1) default 'X' not
null);
insert into T (a) values (1),(2);

db2 "merge into T using ( values 1 ) U (a) on U.a = T.a when matched
then update set B = 'Y' when not matched then insert values (U.a,
'X')";
DB20000I The SQL command completed successfully.

db2 "merge into T using ( with gen(n) as (values 1) select n from
gen ) U (a) on U.a = T.a when matched then update set B = 'Y' when not
matched then insert values (U.a, 'X')"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "using ( with
gen(n)".
Expected tokens may include: "JOIN". SQLSTATE=42601
Yeah, that's what I was getting. I was hoping that Serge would join
in with some funky way of using VALUES to make it work. :)

Nov 15 '07 #12

P: n/a
On Nov 15, 12:42 pm, "Dan van Ginhoven" <danfa...@hotmail.comwrote:
Hello!

Why not export the table to a file, and then use any file oriented language,
like Cobol, C or perl, to do the job.
Then, import or load the file again , either into a worktable and from there
do an update, or if feasible, into the original table.
Because the original requirement was for a single SQL statement.

And because it's cool to do stuff in SQL that C coders think you need
a procedural language for.
Nov 15 '07 #13

P: n/a
------------------------- Commands Entered -------------------------
CREATE TABLE code_table
(id SMALLINT NOT NULL
,code VARCHAR(20)
);
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
INSERT INTO code_table
VALUES
(1, 'XD23414')
,(2, 'LKJ454678')
,(4, '0023821')
,(5, '076994')
,(6, 'AG00086904')
,(7, '834292')
,(8, 'PERTM98')
,(9, 'M7521315')
,(10, 'M 21315')
,(11, ' 8479')
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT * FROM code_table;
--------------------------------------------------------------------

ID CODE
------ --------------------
1 XD23414
2 LKJ454678
4 0023821
5 076994
6 AG00086904
7 834292
8 PERTM98
9 M7521315
10 M 21315
11 8479

10 record(s) selected.
------------------------- Commands Entered -------------------------
UPDATE code_table
SET code =
TRANSLATE(LTRIM(TRANSLATE(code
,'','0ABCDEFGHIJKLMNOPQRSTUVWXYZ'))
,'0',' ')
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT * FROM code_table;
--------------------------------------------------------------------
CODE
------ --------------------
1 23414
2 454678
4 23821
5 76994
6 86904
7 834292
8 98
9 7521315
10 21315
11 8479

10 record(s) selected.

Nov 19 '07 #14

P: n/a
On Mon, 19 Nov 2007 06:22:07 -0800 (PST), Tonkuma
<to*****@fiberbit.netwrote:
>------------------------- Commands Entered -------------------------
CREATE TABLE code_table
(id SMALLINT NOT NULL
,code VARCHAR(20)
);
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
INSERT INTO code_table
VALUES
(1, 'XD23414')
,(2, 'LKJ454678')
,(4, '0023821')
,(5, '076994')
,(6, 'AG00086904')
,(7, '834292')
,(8, 'PERTM98')
,(9, 'M7521315')
,(10, 'M 21315')
,(11, ' 8479')
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT * FROM code_table;
--------------------------------------------------------------------

ID CODE
------ --------------------
1 XD23414
2 LKJ454678
4 0023821
5 076994
6 AG00086904
7 834292
8 PERTM98
9 M7521315
10 M 21315
11 8479

10 record(s) selected.
------------------------- Commands Entered -------------------------
UPDATE code_table
SET code =
TRANSLATE(LTRIM(TRANSLATE(code
,'','0ABCDEFGHIJKLMNOPQRSTUVWXYZ'))
,'0',' ')
;
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT * FROM code_table;
--------------------------------------------------------------------
CODE
------ --------------------
1 23414
2 454678
4 23821
5 76994
6 86904
7 834292
8 98
9 7521315
10 21315
11 8479

10 record(s) selected.
Nov 24 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.