字符出现的第一个位置(比较麻烦的一种)
#7532Assisted Living
#0005622Assisted Living
#7340Assisted Living
#10399Assisted Living
#5263Assisted Living
#5819Assisted Living
以上是一些记录信息,我想在数字与字符之间加一个空格,但是这些字符出现的位置不是很固定,我如何得到字符出现的位置呢,还有如何在字符和数字之间添加一个空格呢
[解决办法]
使用函数:charindex()/patindex()
[解决办法]
declare @test table (test varchar(100))
insert @test
select '#7532Assist1ed Living ' union all
select '#0005622Ass2isted Living ' union all
select '#7340Assist3ed Living ' union all
select '#10399Assis4ted Living ' union all
select '#5263Assist5ed Living ' union all
select '#5819Assist6ed Living '
while exists (select 1 from @test where patindex( '%[0-9][A-Z]% ', test) > 0)
begin
update @test set test = stuff(test,patindex( '%[0-9][A-Z]% ', test)+1,0, ' ') where patindex( '%[0-9][A-Z]% ', test) > 0
end
while exists (select 1 from @test where patindex( '%[A-Z][0-9]% ', test) > 0)
begin
update @test set test = stuff(test,patindex( '%[A-Z][0-9]% ', test)+1,0, ' ') where patindex( '%[A-Z][0-9]% ', test) > 0
end
select * from @Test
/*
#7532 Assist 1 ed Living
#0005622 Ass 2 isted Living
#7340 Assist 3 ed Living
#10399 Assis 4 ted Living
#5263 Assist 5 ed Living
#5819 Assist 6 ed Living
*/