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

Query to search text of all sql server database objects

here the sql query to search releated objects those includes this text:

select
ss.name as [schema],
so.name as [Object],
so.type_desc as [objectType]
FROM sys.objects so
Inner join sys.schemas ss on ss.schema_ID = so.schema_Id
inner join syscomments sc On sc.Id = so.object_ID
where is_ms_shipped =0 ANd sc.text like '%test%'

Read XMLColumn value and Get it in Table format

for Example the XML value is Like :


bbb
vvv
aaa


SELECT r1.value ('./@id', 'varchar(100)') as Attribute, '' as Value
FROM [Table Name] CROSS APPLY [ColumnName].nodes ('Root/D') AS x (r1)

SQL Function for Comma seperator value to return in Table format

Create FUNCTION List_to_vc_table
(@list varchar(8000))
RETURNS @tbl Table (number varchar(1000) not null)
AS
--Returns The Given Comma Specified list in Varchar Table
BEGIN
DECLARE @ID varchar(20), @Pos int

SET @list = LTRIM (RTRIM (@list) )+ ','
SET @Pos = CHARINDEX(',' , @list, 1)
IF REPLACE (@list, ',', ' ') <> ''
BEGIN
WHILE @Pos >0
BEGIN
SET @ID = LTRIM(RTRIM(LEFT(@list, @Pos - 1)))
IF @ID <> ' '
BEGIN
INSERT @tbl (number) values (@ID)
END
SET @list = RIGHT(@list, LEN (@list) - @Pos)
SET @Pos = CHARINDEX(',' , @list, 1)
END
END
return
END

XML Serialization/Deserialization of Collection of Objects

• Make one sEmployee Class with their Property. And one Employeecollection class of that sEmployee Class.

public class sEmployee
{
public string FirstName;
public string LastName;
public int Age;
public sEmployee()
{
}
public sEmployee(string first, string last, int age)
{
this.FirstName = first;
this.LastName = last;
this.Age= age;
}
}

• Implement the CollectionBase for adding multiple employee item in collection by override their Add Method.

public class EmployeeCollection : CollectionBase
{
public EmployeeCollection() { }
public EmployeeCollection(EmployeeCollection value) { this.AddRange(value); }
public EmployeeCollection(sEmployee[] value) { this.AddRange(value); }
public sEmployee this[int index] { get { return ((sEmployee)(List[index])); } set { List[index] = value; } }
public int Add(sEmployee value) { return List.Add(value); }
public void AddRange(sEmployee[] value) { for (int i = 0; i < i =" 0;">

• Now create one sample page for serialization and Deserialization.here i have took one .aspx page and code behind i have wrote this simple code,and saving that data into XML file.

Serialization :

EmployeeCollection empCollection = new EmployeeCollection();
empCollection.Add(new sEmployee("Paresh0", "Patel0", 20));
empCollection.Add(new sEmployee("Paresh1", "Patel1", 21));
empCollection.Add(new sEmployee("Paresh2", "Patel2", 22));

string op = string.Empty;
using (StringWriter writer = new StringWriter(new StringBuilder()))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
xs.Serialize(writer, empCollection);
op = writer.ToString();
}
File.WriteAllText("c:\\emp.xml", op);

Deserialization :

string ip = string.Empty;
EmployeeCollection collection = new EmployeeCollection();
ip = File.ReadAllText("c:\\emp.xml");
using (StringReader reader = new StringReader(ip))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
collection = (EmployeeCollection)xs.Deserialize(reader);
}

Get Comma Seperator Value in one Filed

select STUFF ( (SELECT ',' + [Column_name]
FROM [TableName] WHERE [Criteria]
FOR XML PATH('')), 1, 1, '') AS [DIsplay Name]