遇到的一个sql的问题

T_SDDLJT表中JTBM和SBBM字段的值需要把其中的字符X去掉，例如04401202000114XZD1需要改为04401202000114ZD1。因为是编码，X固定为字段值的第15个字符。
 google了一下，找到了sql语句
UPDATE t_sddljt
   SET jtbm = REPLACE (jtbm, 'X', ''),
       sbbm = REPLACE (sbbm, 'X', '')
 WHERE INSTR (sbbm, 'X') = 15
 如果不是去掉X，而是固定位置的字符，则可以改为
UPDATE t_sddljt
   SET jtbm = REPLACE (jtbm, SUBSTR (jtbm, -4, 1), ''),
       sbbm = REPLACE (sbbm, SUBSTR (sbbm, -4, 1), '')
 WHERE INSTR (sbbm, 'X') = 15
 
 
 ------------------------------------------------------------------------
 oracle中substr函数的用法
 
In oracle/PLSQL, the substr functions allows you to extract a substring from a string.

The syntax for the substr function is:

        SUBSTR( string, start_position, [ length ] )

string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
For example:

    substr('This is a test', 6, 2) would return 'is'
    substr('This is a test', 6) would return 'is a test'
    substr('TechOnTheNet', 1, 4) would return 'Tech'
    substr('TechOnTheNet', -3, 3) would return 'Net'
    substr('TechOnTheNet', -6, 3) would return 'The'
    substr('TechOnTheNet', -8, 2) would return 'On'
   
----------------------------------------------------------------------

REPLACE ( char , search_string [, replacement_string] )

REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.

Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.

一路走回去

真想这样一路走回去，好让我有时间听完我的mp3里的歌，让我有时间思考我的生活，让我有时间在清醒的时候休息。
其实我真的很在乎。my blog, my friend

Published from my blackberry8700