| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-06-12, 04:42
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 3
|
|
|
Replace characters
|
|
Hi am getting the some special characters in my data
Sample data:- "3M™BathroomDisinfectantCleaner , i want to replace all specila characters with some default value, finally i have to get numerics and character values only.
Any one could you help me on this
|
|
02-06-12, 05:19
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Please define(or publish) "all specila characters" and "some default value".
Or define all characters "not specila characters".
"specila characters" may be different by Language, industry, Organization, so on.
Needless to say,
what DB2 version/release and platform OS are you using?
|
|
02-06-12, 05:47
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 3
|
|
|
|
Hi special characters may be @#$%^&™ and default value is "
|
|
02-06-12, 06:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
02-06-12, 06:17
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 3
|
|
not able to handle the special character ™ with translate function
|
|
02-06-12, 07:49
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
See the example.
Example 1: Tested on DB2 9.7.5 on Windows
Code:
------------------------------ Commands Entered ------------------------------
VALUES CAST( TRANSLATE( CAST('3M™BathroomDisinfectantCleaner' AS VARGRAPHIC(50))
, CAST('' AS GRAPHIC(1))
, CAST('@#$%^&™' AS GRAPHIC(7))
)
AS VARCHAR(50)
)
;
------------------------------------------------------------------------------
1
--------------------------------------------------
3M BathroomDisinfectantCleaner
1 record(s) selected.
|
|
02-06-12, 08:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
OR
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
VALUES CAST( TRANSLATE( '3M™BathroomDisinfectantCleaner'
, ' ' || CAST(' ' AS GRAPHIC(1))
, '@#$%^&' || CAST('™' AS GRAPHIC(1))
)
AS VARCHAR(50)
)
;
------------------------------------------------------------------------------
1
--------------------------------------------------
3M BathroomDisinfectantCleaner
1 record(s) selected.
|
|
02-06-12, 08:25
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Replace all characters other than 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789' with a blank.
Example 3:
Code:
------------------------------ Commands Entered ------------------------------
VALUES CAST(
TRANSLATE(
'3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
, CAST('' AS GRAPHIC(1))
, CAST(
TRANSLATE(
'3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
, ''
, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
, ''
) /* TRANSLATE( */
AS VARGRAPHIC(127)
) /* CAST( */
) /* TRANSLATE( */
AS VARCHAR(100)
) /* CAST( */
;
------------------------------------------------------------------------------
1
----------------------------------------------------------------------------------------------------
3M BathroomDisinfectantCleaner Kanji 000 ZZZ
1 record(s) selected.
|
Last edited by tonkuma; 02-06-12 at 08:29.
Reason: Add comments in sample code.
|
02-07-12, 01:02
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 4
|
|
Thanks Tonkuma...I m also searching the same...It works fine..
Can u explain this translate function alone:
TRANSLATE(
'3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
, ''
, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789'
, ''
)
Thanks in advance.
|
|
02-07-12, 01:20
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Please try to execute the function by yourself and see the result.
It will return all non alphanumeric characters(specified by 3rd argument) in the first argument.
Note: 2nd and 4th arguments are zero length string.
Then TRANSLATE function removed characters in 3rd argument from first argument.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Replace characters
