delete characters between parentheses? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

delete characters between parentheses?

delete characters between parentheses?# Database - 数据库

a*t2008-06-16 07:06

1 楼

Hi,
I can do this in sql server -
select * where cln1 like '%(%)%'
however, if I want to delete the characters between parentheses, I can't use
replace( cln1, '(%)','').
Anyone knows how to do it?
Thanks a lot!

B*g2008-06-16 07:06

2 楼

like followed by a pattern.
replace use string.
I know how to do it using regular expression in oracle 10g+.
As I know you have to create your own function to do regular expression
replace in sql server 2005(I may be wrong)

use

【在 a*******t 的大作中提到】

: Hi,
: I can do this in sql server -
: select * where cln1 like '%(%)%'
: however, if I want to delete the characters between parentheses, I can't use
: replace( cln1, '(%)','').
: Anyone knows how to do it?
: Thanks a lot!

a*t2008-06-16 07:06

3 楼

Thanks for your response.
Exactly, the wildcard character doesn't work for replace in sql server 2005.
What is the logic in oracle 10g+ for this please?

B*g2008-06-16 07:06

4 楼

呵呵， I hate regular expression.
select col, regexp_replace(col, '[\(]{1}[^)]*[\)]{1}', '')
from table
where col like '%(%)%'

2005.

【在 a*******t 的大作中提到】

: Thanks for your response.
: Exactly, the wildcard character doesn't work for replace in sql server 2005.
: What is the logic in oracle 10g+ for this please?

j*n2008-06-16 07:06

5 楼

一个笨点的招：
用select 找出来放到 temp table, 然后一个个repalce.

B*g2008-06-16 07:06

6 楼

为啥sql server不搞regular expression？

【在 j*****n 的大作中提到】

: 一个笨点的招：
: 用select 找出来放到 temp table, 然后一个个repalce.

j*n2008-06-16 07:06

7 楼

其实也有，看看online book 上面介绍了的。我只是很少做类似的工作，不是太熟罢了。
2005 对 regular expression 也作了相应的加强。

【在 B*****g 的大作中提到】

: 为啥sql server不搞regular expression？

a*t2008-06-16 07:06

8 楼

Thanks so much! This is helpful!

a*t2008-06-16 07:06

9 楼

get character index of '('
get character index of ')'
get substring until '('
get substring starting from ')'
new string = the two above substring add together