RSS

Monthly Archives: July 2014

Split string on multiple delimited pass function sql


In This Article , i will write function that  split the string based onmultiple delimiters pass  and also remove the Extra spaces around the string.

--Create this user deinfed function.
 
CREATE FUNCTION dbo.[UDF_Split_String_On_Multiple_Delimiters]
(
      @String VARCHAR(MAX),  -- Variable for string
      @delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
 
        DECLARE @Xml AS XML
        DECLARE @REVISED_STRING VARCHAR(MAX)
 
        ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
        N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
        N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
        N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
        FROM N3 AS X, N3 AS Y)
 
        SELECT @REVISED_STRING=STUFF((SELECT '' + (Case When
                PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
                Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
        FROM N4 Nums WHERE Nums.n<=LEN(@String)  FOR XML PATH('')),1,0,'')
 
        SET @Xml = cast(('<a>'+replace(@REVISED_STRING,
                ',','</a><a>')+'</a>') AS XML)
 
        INSERT INTO @Table SELECT A.value('.', 'varchar(max)')
                as [Column] FROM @Xml.nodes('a') AS FN(a)
 
RETURN
END
GO
 
Call Function (One way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad,ahmed ; waleed; Ali',',;:')
 
Call Function(Second Way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad[}]ahmed [}] waleed[}]Ali','[}]')
GO
 
Advertisements
 
Leave a comment

Posted by on July 23, 2014 in SQL Query

 

Tags:

Devexpress Gridview RepositoryItemCheckEdit changes immediatelly


If you have a Gridview with the first column edited with an RepositoryItemCheckEdit and with the datasource an runtime created datatable.
What I want to obtain and I not succeeded yet is to count the rows from the datatable with check on the first column and put the result in a labelimmediatelly after user changes check state of repository.

but if you check/Uncheck checkbox   RepositoryItemCheckEdit  Event then your Count not match with Gridview Checkbox.if you use different event of Gridview . the solution of your problem is..

[VB.NET]
Private Sub repositoryItemCheckEdit1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
    gridView1.PostEditor()
    CountChecked()
End Sub

The PostEditor method posts the edited value to the underlying source. I’ve attached a sample demonstrating how it works. Please test it and let us know your result.

the CountChecked method as follows:

[VB.NET]
Private Sub CountChecked()
    Dim checkedCount As Integer = 0
    Dim dt As DataTable = CType(gridControl1.DataSource, DataTable)
    For i As Integer = 0 To dt.Rows.Count - 1
        If CBool(dt.Rows(i)("Check")) Then
            checkedCount += 1
        End If
    Next i
    labelControl1.Text = "Checked: " & checkedCount.ToString()
End Sub

Reference :

https://www.devexpress.com/Support/Center/Question/Details/Q583442

 

 

 

 

 

 

 
1 Comment

Posted by on July 4, 2014 in WinForm

 

Tags: ,