SQL2008 -condition on # of occurances of a character in a string

[CarolCook]

Hi:
Is there anyway in a where clause to condition an update query on the number of instances something occurs in a string. I only want to update records where the pipe appears once:

update abc|def
but not abc|def|123

thanks in advance

 

[fxthoth]

Hello, try this:

declare @myTable table (
	id int identity primary key not null,
	foo varchar(100) not null
)

insert into @myTable values('abc|def')
insert into @myTable values('abc|def|')
insert into @myTable values('abcd|efgh')
insert into @myTable values('abcd|efgh|ij|kl')

--select values with one pipe
select foo from
	@myTable
where (LEN(foo) - LEN(REPLACE(foo, '|' ,'')))=1

--update values with one pipe(syntax for sql server 2005 or 2008)
update @myTable set foo='new value'
	from @myTable
	where (LEN(foo) - LEN(REPLACE(foo, '|' ,'')))=1

--modified result
select foo from
	@myTable

 

[fxthoth]

Of course, you can use directly:

update @myTable set foo='new value'
    where (LEN(foo) - LEN(REPLACE(foo, '|' ,'')))=1

My mistake...

 

[CarolCook]

Excellent!
Thanks so much.