地址 截取 换行 SQL怎么写？？？？？？

ANRRY258

字符串: RM 3801, CABLE TV TOWER9 HOI SHING ROADTSUEN  WAN NTHONG KONG,  HK

-->
RM 3801, CABLE TV TOWER9 HOI SHING ROADTSUEN （44位）换行
WAN NTHONG KONG,  HK


以上是一串地址，
现在要将地址第一行显示不能超过44位，但是截取的时候不能将一个英文单词分开
如果算上最后一个单词超过44位，就截取该单词前面那个单词为止（根据空格来区分是否一个完整的单词），
如：RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG (50位)
--> RM 3801, CABLE TV TOWER9 HOI SHING WAN (38位)
NTHONG  KONG
如果加上 NTHONG 就变成 45位 ，所以要取他前面那个单词为止，换行

可写函数或SQL

marusu

因为有事情没有测试其他例子，试试下面这个吧，可能有点长，可以改写一个get function应该能简化点。

可能还有其他简单的办法，目前我只能想到这个。


SELECT DECODE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',45,1),' ',SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)||CHR(10)||SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',46),SUBSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),1,INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',''))))||CHR(10)||SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ','')))+1))
                FROM DUAL

marusu

修正版:

SELECT DECODE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',45,1),' ',SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)||CHR(10)||SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',46),SUBSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),1,INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',''))))||CHR(10)||TRIM(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',''))))))
  FROM DUAL
;

marusu

再次修正：因为之前几次没有考虑第二行首字母是空格的出现，第二次加上一个但是还少了一个，这次应该没错了，剩下自己调试一下吧。

SELECT DECODE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',45,1),' ',SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)||CHR(10)||TRIM(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',46)),SUBSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),1,INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',''))))||CHR(10)||TRIM(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',INSTR(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',1,LENGTH(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44)) - LENGTH(REPLACE(SUBSTR('RM 3801, CABLE TV TOWER9 HOI SHING WAN NTHONG KONG',1,44),' ',''))))))
  FROM DUAL

ANRRY258

marusu 发表于 2012/11/13 15:12
再次修正：因为之前几次没有考虑第二行首字母是空格的出现，第二次加上一个但是还少了一个，这次应该没错了 ...

多谢！

我已经写了
select substr(
      'RM 3801, CABLE TV TOWER9 HOI SHINGROADTSUENWAN NTHONG KONG,  HK',1,
      instr(substr(
      'RM 3801, CABLE TV TOWER9 HOI SHINGROADTSUENWAN NTHONG KONG,  HK',1,44),
      ' ',-1)) result
      from  dual;

marusu

只要一行吗？那我看错了。。。晕菜了。。。

ANRRY258

其实，后来发现 字符串中可以 用 CHR(10) 强制换行。。。。

davidma

呵呵，够强大的啊

davidma

最好是写成函数，可能会好些啊