question

David 2 1 avatar image
David 2 1 asked

How To Query A Column For 2 Or More Repeating Characters?

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.

t-sqlsql-server-2000select
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nathan Skerl avatar image
Nathan Skerl answered

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            
*/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

You could use like for this:

Where col LIKE '%#%#%'            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave P 1 avatar image
Dave P 1 answered

Use the PATINDEX function with the pattern '%##%', this will pick up two or more #'s in the expression and return a non-zero value.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave P 1 avatar image
Dave P 1 answered

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 ;            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gangtom avatar image
gangtom answered
update set numHases = len(replace(col, '#', '# ')) - len(col) This will give you the no. of hashes each cell has. Or to select rows with more than 2 hashes, select * from abc where len(replace(col, '#', '# ')) - len(col) > 2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.