Jeffrey Jeffrey - 4 months ago 13
SQL Question

TSQL count number of declared characters in string

Ive got the following script, which should count the number of characters declared in @Char that is found in @password but its not working, can anyone point me in the right direction?

Declare @password as varchar(25), @Result int = 0, @Char varchar(max), @index BigInt = 0

Set @password = 'su8cd023m6!$^j3'

Set @Char = '%[a-z]%'

DECLARE @passwordLen int = len(@password)

SET @index = CHARINDEX(@password, @Char, @index)
While @index > 0
BEGIN
SET @Result = @Result + 1;
SET @index = CHARINDEX(@password, @Char, @index + @passwordLen)
END

Print @Result


The above should return 6 as there are 6 alpha characters in @password

Answer
Declare @password as varchar(25), @Result int = 0,  @Char varchar(max), @index BigInt = 0

Set @password = 'su8cd023m6!$^j3'

  WHILE PATINDEX('%[^A-z]%',@password)>0
        SET @password = STUFF(@password,PATINDEX('%[^A-z]%',@password),1,'') 

Set @Result = Len(@password)

Print @password
Print @Result
Comments