怎样在SQLSERVER中实现这样的操作，往某个字段的所有记录的第一个数字前加一个空格?

Question

怎样在SQLSERVER中实现这样的操作，往某个字段的所有记录的第一个数字前加一个空格?

Answer 1

http://topic.csdn.net/u/20081124/10/c195f43f-1f05-4dd2-a88d-2da2c4b89810.html

Answer 2

update tb set col=stuff(col,patindex('%[1-9]%', col),0,' ')

Answer 3

select stuff(col,patindex('%[1-9]%', col),0,' ') from tb
--剛才問過,遇到什麼問題?

Answer 4

update tb set col=stuff(col,patindex('%[0-9]%', col),0,' ') --0-9

Answer 5

create table tb(id int, name varchar(50))
insert into tb values(1,'a1a')
insert into tb values(2,'bb2')
insert into tb values(3,'cc3')
insert into tb values(4,'dd4')
insert into tb values(5,'e333e5')
insert into tb values(6,'fdsaf22f')
insert into tb values(7,'gsadedd3434fg')
insert into tb values(8,'asafdsf34534hh')
insert into tb values(9,'i44554i')
goselect name_old = name , name_new = left(name , patindex(N'%[0-9]%',name) - 1) + ' ' + substring(name , patindex(N'%[0-9]%',name) + 1 ,len(name)) from tb where patindex(N'%[0-9]%',name) > 0drop table tb
/*
name_old                                           name_new                                                                                              
-------------------------------------------------- ----------------------------------------------------------------------------------------------------- 
a1a                                                a a
bb2                                                bb 
cc3                                                cc 
dd4                                                dd 
e333e5                                             e 33e5
fdsaf22f                                           fdsaf 2f
gsadedd3434fg                                      gsadedd 434fg
asafdsf34534hh                                     asafdsf 4534hh
i44554i                                            i 4554i（所影响的行数为 9 行）
*/