In SQL Server 2000 is it possible to query a column in a table for 2 or more repeating characters? For example I'd like to know which rows contain the '#' character more than once.
Thanks in advance.
Answer by Nathan Skerl ·
You can also play with replace to "count" the number of characters like so:
declare @s varchar(15) set @s = 'abc#def#ghi' if (len(@s) - len(replace(@s, '#', ''))) > 1 begin print 'more than one found' end else begin print 'less than one found' end /* against table select * from dbo.YourTable where len(YourColumn) - len(replace(YourColumn, '#', ''))) > 1 */
Answer by Dave P 1 ·
I think I misunderstood your question. My previous post assumed you were looking for adjacent characters. This will do what you want: count the instances of @c in @in.
CREATE FUNCTION dbo.fn_strcount ( @in varchar(8000), @c char ) RETURNS integer AS BEGIN DECLARE @i int, @l int, @out int ; DECLARE @ch char ; SELECT @l = LEN(@in), @i = 1, @out = 0 ; WHILE ( @i <= @l ) BEGIN SET @ch = SUBSTRING(@in, @i, 1) ; IF ( @ch = @c ) SET @out = @out + 1 ; SET @i = @i + 1 ; END ; RETURN (@out) ; END ;