Find € within a table

kwiltfong · #1 (**permalink**) 01-29-09, 14:48

I'm looking for a query to search every field of a specified table for a certain character. In this case I'm looking for the euro sign (€). Any help on this is much appreciated.

Thank you,

Keith

ARWinner · #2 (**permalink**) 01-29-09, 15:03

Try the LOCATE function. Or you could use the LIKE expression.

Andy

kwiltfong · #3 (**permalink**) 01-29-09, 15:31

Thank you for the quick response.

I'm really looking for something to check EVERY column within a table. How would I do that with LIKE or LOCATE.

Thanks,

Keith

ARWinner · #4 (**permalink**) 01-29-09, 16:07

select * from mytable where col1 like '%$%' or col2 like '%$%' ...

Andy

kwiltfong · #5 (**permalink**) 01-29-09, 16:46

Yes, Thank you. I was just wondering if there is a method to query each column in the table without specifically naming each one.

n_i · #6 (**permalink**) 01-29-09, 16:48

Certainly. You could query SYSCAT.COLUMNS, then construct and execute a dynamic SQL statement that takes into account each column's datatype.

kwiltfong · #7 (**permalink**) 01-30-09, 09:29

That's what I was looking for! Thank you!

sathyaram_s · #8 (**permalink**) 01-30-09, 11:12

This should generate the SELECT Statements you need:

Code:

with tab(tsch,tname) as 
(
-- Select only the required tables or list them 
--values ('SYSCAT','SEQUENCES') ,('SYSCAT','TABLES')
select tabschema,tabname from syscat.tables where type='T'
),
star(ts,tn,cno,concatcol,dtype) as 
(
select tsch,tname,-1,varchar('''A''=''B''',5000),'' from sysibm.sysdummy1,tab
union all
select tsch,tname,colno,concatcol||case when typename in 
-- include other character data types 
('CHARACTER','VARCHAR','CLOB','LONG VARCHAR')
 then ' OR LOCATE(''€'','||colname||') > 0 ' else '' end ,
typename  from star s,syscat.columns c, tab where c.colno=cno+1 and tabname=tname and tabschema=tsch and 
  s.ts=c.tabschema and s.tn=c.tabname  )
  select rtrim('select * from '||rtrim(ts)||'.'||rtrim(tn)||' where ')||concatcol
from star so
where cno=(select max(cno) from star si where si.ts=so.ts and si.tn=so.tn )

Peter.Vanroose · #9 (**permalink**) 01-31-09, 09:44

Sathyaram,

You should put this in the Script Library ;-)
Maybe I'll add the variant for DB2 on z/OS there.

dr_te_z · #10 (**permalink**) 02-03-09, 02:11

What "codeset" do you use? I've had trouble with the euro sign when converting databases. If I recall it right then the "1208" codeset does not have the euro sign at all, but the windows client just puts the euro sign in code "1252" in there and that is represented correctly when you retrieve that data again..... But in the database this is not recognized! The binary representation in "1208" is meaningless. So you can have errors in your database for ever without noticing it.

Peter.Vanroose · #11 (**permalink**) 02-03-09, 02:33

Quote:

Originally Posted by dr_te_z

What "codeset" do you use? I've had trouble with the euro sign when converting databases. If I recall it right then the "1208" codeset does not have the euro sign at all

"1208" certainly has the euro sign: it is UTF-8, one of the encodings that is able to represent the full Unicode character set.
Beware that UTF-8 is a variable length encoding, i.e., some characters (including the euro sign) are represented with more than one byte.
Most likely your interface (graphical interface, editor, ...) either didn't recognise that the data is UTF-8 encoded, so it will show single bytes as single characters and the euro sign comes out as 3 bytes. Or the interface does not support the full UTF-8 range (maybe just the 1-byte and 2-byte characters); or maybe the GUI does not have an euro sign in its graphical font.

What exactly did you do and what exactly are you seeing, and in which environment?

dr_te_z · #12 (**permalink**) 02-03-09, 03:55

Quote:

Originally Posted by Peter.Vanroose

"1208" certainly has the euro sign: it is UTF-8,

Peter, do not confuse codeset and codepage

Old: Windows 2003-32 bit DB2 V8.2
New: Windows 2003-64 bit DB2 V9.5
I made scritps to export the data into *.ixf files (so the data was translated from 1208 to 1252 by the DB2CMD CLI) and load those files in the new database (translated back from 1252 to 1208). In this process our EUR sign got corrupted (in CLOB columns).
It seemed that the 1208-CLOB columns were populated with 1252 data. This did not survive the translations.

char 0x80 (euro sign in Windows-1252) became 0x1a (Substitute character in 1208)

This

Code:

db2set -g DB2CODEPAGE=1208

did not help (made it even worse).
We ended up cleaning up our old database

Code:

 
UPDATE table  SET column = REPLACE(VARGRAPHIC(column), X'80', 'EUR');

The problem (as I see it):
1 - codeset 1208 does not have het EUR sign
2 - DB2-CLOB columns can be populated with (binary?) data which does not adhere to its own codeset standards.

Peter.Vanroose · #13 (**permalink**) 02-03-09, 04:46

Quote:

Originally Posted by dr_te_z

...load those files in the new database (translated back from 1252 to 1208)... char 0x80 (euro sign in Windows-1252) became 0x1a (Substitute character in 1208)

OK, then the problem must be in this (back)conversion.
Since version 8, DB2 takes the responsibility to translate a character from the codeset of the application to the codeset of the table (and vice versa), so encoding should be 100% transparant.
Most likely, in the communication between the application sending the 1252-character and DB2, either the application failed to identify itself as speaking 1252, or DB2 erroneously doesn't know that char 0x80 is the Euro sign.
(I don't know Windows-1252 well enough to know which of the two is wrong here.)
I would suggest you verify the connection settings with DB2 to make sure both sides "speak the same language" (viz. Windows-1252, which IBM calls "CCSID 1252"); maybe you need "db2set -g DB2CODEPAGE=1252" for this? I'm not familiar with the "db2set -g" command, sorry.

CCSID 1208 most certainly *has* the euro sign; it's represented as 0xE2:0x82:0xAC (so observe it's represented with three bytes!)

P.S. for a good explanation of the difference between "codeset" and "codepage", see e.g. CCSID - Wikipedia, the free encyclopedia
So, Unicode is a *codepage*, while UTF-8 (aka CCSID-1208) is a *codeset*.
In the context of physical transfer of data, only the codeset is relevant.

dr_te_z · #14 (**permalink**) 02-03-09, 06:14

Thanks Peter. For me and my client this is all in the past now, but we've got it documented here

Internet Services

Web Development

Digital Marketing

Consumer Tech