| redbook |  redbook  |   redbook   | red"book |

+         +           +             +          +

1 row in set (0.00 sec)

Since DB2 UDB has been designed and implemented according to the ANSI 

standard, it accepts single quotes as a string delimiter. Double quotes are used 

in DB2 for delimiting SQL identifiers; for example, in a migration project you may 

want to create table or column names in lower or mixed character notation. 

Example 7 8 shows how DB2

handles strings. Similar results will be achieved 

when MySQL runs in ANSI mode.

Example 7 8   DB2 string handling

db2 => select  redbook ,  "redbook" ,  ""redbook"" ,  red  book  from t1

1       2         3           4

redbook "redbook" ""redbook"" red book

Table 7 2 provides an overview of MySQL string related functions, and how these 

can be converted to DB2 UDB.

Table 7 2   MySQL and DB2 UDB string related function

MySQL

DB2 UDB

Comment

ASCII in string cast: 

ASCII in string cast: 

Returns ASCII code value, 

select ascii( a ) from t1

select ascii( a ) from t1

for example 

values(ascii( i )) returns 

105

automatic num >string 

Require explicit casting

Please refer to:

convert

 Implicit casting of data 

automatic string >num 

types  on page 163

convert

concatenation with +

CONCAT or ||

Concatenation with +.

CONCAT(`a','b','c')

(`a'||'b'||'c')

Use || to implement 

CONCAT(list)

ELT(n,str1,str2,str3,...)

CASE

Returns the 

n

th string or 

NULL. Use CASE 

expression or UDF

FORMAT:

no equivalent, 

Refer to UDF in 

select 

implementing use UDF

Appendix A.2,  Sample 

format(1234.5555.2) 

code for FORMAT 

returns 1,234.56

function  on page 337

162 

MySQL to DB2 UDB Conversion Guide