×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

examples

1) for replace():
The following example replaces occurrences of "J" with "BL":

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;

Changes
--------------
BLACK and BLUE

2) for translate():

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;

License
--------
9XXX999


The following statement returns a license number with the characters removed and the digits remaining:

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
"Translate example"
FROM DUAL;

Translate example
-----------------
2229

have fun!!
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / Access里面有个memo field,现在我需要写代码,replace一个词。怎么写啊?谢谢。
    • up
      • 是用query还是 programming code?
        • I am using code now. I think i might know how to do it. I am just doing a test now, using instr and mid function. Thanks.
    • 要写一段code了,取出来后,用substring(),replace()之类的function处理。
      • 谢谢秧子。我选你当先进工作者。:P
    • 在update query 里直接用replace
      • yes, found it. Thanks
      • 是要在memo里替换若干词,不是全部换。
        • 比如说吧,你有个memo field called "risk", 你要把里面的一个词'internal' 改成'external', 直接用replace(risk,"Internal","external")就可以啦
          • ORACLE能这么简单做到同样功能么?
            • N年不用oracle了,还真不知道了
              • no problem, and even more...
                • how?please
                  • do you google?
                  • examples
                    1) for replace():
                    The following example replaces occurrences of "J" with "BL":

                    SELECT REPLACE('JACK and JUE','J','BL') "Changes"
                    FROM DUAL;

                    Changes
                    --------------
                    BLACK and BLUE

                    2) for translate():

                    SELECT TRANSLATE('2KRW229',
                    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                    '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
                    FROM DUAL;

                    License
                    --------
                    9XXX999


                    The following statement returns a license number with the characters removed and the digits remaining:

                    SELECT TRANSLATE('2KRW229',
                    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
                    "Translate example"
                    FROM DUAL;

                    Translate example
                    -----------------
                    2229

                    have fun!!
                    • 有意思,可惜俺手上没有oracle,不然到可以试试
                    • 好玩。