SQL Question - Special Character

Jobi M B225 Augustine

SQL Question - Special Character
Hi List,

Is there a way that we can select some special characters in SQL?

For example

SELECT CHAR (') , ACCT_NUM, CHAR(')
FROM TABLE A
;

The result I'm expecting is '1234567' for the ACCT_NUM 1234567 from table A.

Here I'm trying to select the special character quote( ' ) in the SQL.

Thanks in advance.

Jobi.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Michael Ebert

Re: SQL Question - Special Character
(in response to John Hardy)
Sure:

SELECT '''' CONCAT SUBSTR(CHAR(ACCT_NUM), 2) CONCAT '''' FROM...

(use 4 quotes in a row). The SUBSTR removes a leading blank (where the
minus goes for negative numbers). You may have to use the DECIMAL function
as well depending on the datatype of ACCT_NUM.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



Hi List,

Is there a way that we can select some special characters in SQL?

For example

SELECT CHAR (') , ACCT_NUM, CHAR(')
FROM TABLE A
;

The result I'm expecting is '1234567' for the ACCT_NUM 1234567 from table
A.

Here I'm trying to select the special character quote( ' ) in the SQL.

Thanks in advance.

Jobi.



Jobi M B225 Augustine

Re: SQL Question - Special Character
(in response to Michael Kaplan)
Hi Michael,

Yes it worked !! Thank you very much !!

SELECT X'7D' CONCAT ACCT_NUM CONCAT X'7D'
FROM TABLE A
;


Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-5344 (6-5344)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


-----Original Message-----
From: Michael Kaplan [SMTP:[login to unmask email]
Sent: Sunday, December 23, 2001 5:01 PM
To: [login to unmask email]
Subject: Re: SQL Question - Special Character

Hi Augustine,

You can solve this problem using HEXA presentation of special
symbol.
Check ( in ISPF Edtor) what is HEXA of (') and then build the
following :

SELECT X'NN' CONCAT ACCT_NUM CONCAT X'NN'
FROM TABLE A

Where NN - HEXA of Asterix.

Btw, I assumed ACCT-NUM is of CHAR type, if not, you have to
convert your
numeric type to
character type using CHAR function. One more tip, in your case, use
STRIP
function
on char type to throw blanks.

Bye,

Michael Kaplan
DBA and IT Consultant
E-mail : [login to unmask email]
Mobile phone : 058-525276





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of
Augustine, Jobi M B225
Sent: Sunday, December 23, 2001 8:59 PM
To: [login to unmask email]
Subject: SQL Question - Special Character


Hi List,

Is there a way that we can select some special characters in SQL?

For example

SELECT CHAR (') , ACCT_NUM, CHAR(')
FROM TABLE A
;

The result I'm expecting is '1234567' for the ACCT_NUM 1234567 from
table A.

Here I'm trying to select the special character quote( ' ) in the
SQL.

Thanks in advance.

Jobi.



----------------------------------------------------------------------------
--
CONFIDENTIALITY NOTICE: If you have received this e-mail in error,
please
immediately notify the sender by e-mail at the address shown. This
e-mail
transmission may contain confidential information. This information
is
intended only for the use of the individual(s) or entity to whom it
is
intended even if addressed incorrectly. Please delete it from your
files if
you are not the intended recipient. Thank you for your compliance.©
Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



visit the
DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can be





can

------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Michael Kaplan

Re: SQL Question - Special Character
(in response to Jobi M B225 Augustine)
Hi Augustine,

You can solve this problem using HEXA presentation of special symbol.
Check ( in ISPF Edtor) what is HEXA of (') and then build the following :

SELECT X'NN' CONCAT ACCT_NUM CONCAT X'NN'
FROM TABLE A

Where NN - HEXA of Asterix.

Btw, I assumed ACCT-NUM is of CHAR type, if not, you have to convert your
numeric type to
character type using CHAR function. One more tip, in your case, use STRIP
function
on char type to throw blanks.

Bye,

Michael Kaplan
DBA and IT Consultant
E-mail : [login to unmask email]
Mobile phone : 058-525276





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Augustine, Jobi M B225
Sent: Sunday, December 23, 2001 8:59 PM
To: [login to unmask email]
Subject: SQL Question - Special Character


Hi List,

Is there a way that we can select some special characters in SQL?

For example

SELECT CHAR (') , ACCT_NUM, CHAR(')
FROM TABLE A
;

The result I'm expecting is '1234567' for the ACCT_NUM 1234567 from table A.

Here I'm trying to select the special character quote( ' ) in the SQL.

Thanks in advance.

Jobi.


----------------------------------------------------------------------------
--
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is
intended even if addressed incorrectly. Please delete it from your files if
you are not the intended recipient. Thank you for your compliance.©
Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++








John Hardy

Re: SQL Question - Special Character
(in response to Jobi M B225 Augustine)
..... or alternatively, you could use:

SELECT '''' || ACCT_NUM || ''''
FROM TABLE A
;

The double-apostrophe can be used to indicate a single apostrophe in a
string of text (as in most programming languages).



On Sun, 23 Dec 2001 16:59:23 -0500, Augustine, Jobi M B225
<[login to unmask email]> wrote:

>Hi Michael,
>
>Yes it worked !! Thank you very much !!
>
>SELECT X'7D' CONCAT ACCT_NUM CONCAT X'7D'
>FROM TABLE A
>;
>
>
>Thanks and best regards,
>
>Jobi Augustine
>CIGNA Systems - ES IM &T
>* Tel (860) 226-5466 (6-5466)
>* Fax (860) 226-5344 (6-5344)
>* Mail [login to unmask email] <mailto:[login to unmask email]>
>
>Confidential, unpublished property of CIGNA. Do not duplicate or
distribute.
>Use and distribution limited solely to authorized personnel. Copyright2001
>CIGNA.
>
>
> -----Original Message-----
> From: Michael Kaplan [SMTP:[login to unmask email]
> Sent: Sunday, December 23, 2001 5:01 PM
> To: [login to unmask email]
> Subject: Re: SQL Question - Special Character
>
> Hi Augustine,
>
> You can solve this problem using HEXA presentation of special
>symbol.
> Check ( in ISPF Edtor) what is HEXA of (') and then build the
>following :
>
> SELECT X'NN' CONCAT ACCT_NUM CONCAT X'NN'
> FROM TABLE A
>
> Where NN - HEXA of Asterix.
>
> Btw, I assumed ACCT-NUM is of CHAR type, if not, you have to
>convert your
> numeric type to
> character type using CHAR function. One more tip, in your case,
use
>STRIP
> function
> on char type to throw blanks.
>
> Bye,
>
> Michael Kaplan
> DBA and IT Consultant
> E-mail : [login to unmask email]
> Mobile phone : 058-525276
>
>
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf
>Of
> Augustine, Jobi M B225
> Sent: Sunday, December 23, 2001 8:59 PM
> To: [login to unmask email]
> Subject: SQL Question - Special Character
>
>
> Hi List,
>
> Is there a way that we can select some special characters in SQL?
>
> For example
>
> SELECT CHAR (') , ACCT_NUM, CHAR(')
> FROM TABLE A
> ;
>
> The result I'm expecting is '1234567' for the ACCT_NUM 1234567 from
>table A.
>
> Here I'm trying to select the special character quote( ' ) in the
>SQL.
>
> Thanks in advance.
>
> Jobi.
>
>
>
>---------------------------------------------------------------------------
-
> --
> CONFIDENTIALITY NOTICE: If you have received this e-mail in error,
>please
> immediately notify the sender by e-mail at the address shown. This
>e-mail
> transmission may contain confidential information. This
information
>is
> intended only for the use of the individual(s) or entity to whom it
>is
> intended even if addressed incorrectly. Please delete it from your
>files if
> you are not the intended recipient. Thank you for your
compliance.©
> Copyright 2001 CIGNA
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>visit the
> DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
>can be
>
>
>
>
>visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
list
>can
>
>---------------------------------------------------------------------------
---
>CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is
intended even if addressed incorrectly. Please delete it from your files
if you are not the intended recipient. Thank you for your compliance.©
Copyright 2001 CIGNA
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>

http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-
[login to unmask email]