Count the difference of character in two string in SQL Server

Create the sql function and use it like below :


--Select dbo.DiffCount('BOB', 'BOB')
CREATE FUNCTION dbo.DiffCount(@Str1 varchar(500), @Str2 varchar(500))
Returns int As
BEGIN
DECLARE @len1 INT,
@len2 INT,
@DiffLen INT,
@CharIndex INT,
@Inc INT,
@diffcount INT

SELECT @len1 = DATALENGTH(@str1),
@len2 = DATALENGTH(@str2),
@DiffLen = @len1 - @len2,
@diffcount = 0,
@Inc = 1

IF @len1 <= @len2 BEGIN

WHILE @Inc <= @len2 BEGIN

IF SUBSTRING(@str1,@inc,1) = NULLIF(SUBSTRING(@str2,@inc,1),' ') BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END
END ELSE IF @len1 > @len2 BEGIN
WHILE @Inc <= @len1 BEGIN

IF NULLIF(SUBSTRING(@str1,@inc,1),' ') = SUBSTRING(@str2,@inc,1) BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END

END
RETURN @diffcount
END

Retrieve comma separated values into a table

Hi ,

You can use this table vale function to get the comma separated string into form of table retrieve value.

just create this function and select result from that .

--SELECT * from dbo.CommaSeparatedToTableVale('app,birds,cow,paresh,dhaval')
CREATE FUNCTION CommaSeparatedToTableVale
(
@str VARCHAR(100)
)
RETURNS @Table
TABLE(rec VARCHAR(50))
AS
BEGIN
DECLARE @len INT
DECLARE @inc int
DECLARE @offset INT
DECLARE @newstr VARCHAR(100)

select @len = DATALENGTH(@str),
@inc = 0,
@offset = 0

while @inc <= @len+1 begin

IF ISNULL(@newstr,'') = '' BEGIN
SELECT @offset = charindex(',',@str)
INSERT INTO @Table
SELECT SUBSTRING(@str,0,@offset)
SELECT @newstr = SUBSTRING(@str,@offset+1,@len)
END ELSE BEGIN
SELECT @offset = charindex(',',@newstr)
if @offset = 0 begin
INSERT INTO @Table
SELECT @newstr
break
end else begin
INSERT INTO @Table
SELECT SUBSTRING(@newstr,0,@offset)
end
SELECT @newstr = SUBSTRING(@newstr,@offset+1,@len)
END
SET @len = @len - @offset
SET @inc = @inc + 1
end
--RETURN @table
return
END

And you may also refer this for using XML :

http://sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html