MS-sq字段截取

SQL code

表中有这么个字段      Name-----------------------------      aa        aa[1]      aa[2]      aa(1)      bb      cc      cc[1]我想 要的效果aa  4bb  1cc  2 

不知道表达清楚没有。就是凡是有()和[]的 都当做没有处理

不知道怎么写，请大家帮我下

[解决办法]

SQL code

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([Name] varchar(5))insert [tb]select 'aa' union allselect 'aa[1]' union allselect 'aa[2]' union allselect 'aa(1)' union allselect 'bb' union allselect 'cc' union allselect 'cc[1]'goselect left(name,charindex('[',replace(name,'(','[')+'[')-1) as name,count(1) as cnt from tbgroup by left(name,charindex('[',replace(name,'(','[')+'[')-1)/**name  cnt----- -----------aa    4bb    1cc    2(3 行受影响)**/
[解决办法]
SQL code
gocreate table #s(col varchar(10))insert #sselect 'B3' union allselect 'C1' union allselect 'D\4' union allselect '4MD' union allselect 'AR/12BD';with tas(select substring(col+' ',patindex( '%[0-9]% ',col+' '),len(col+' ')) as col1 from #s)select left(col1+' ',patindex( '%[^0-9]% ',col1+' ')-1) as col from t/*col314124*/如果是无规则字符串中的截取数字请参考以上方法