Generating and splitting a delimited string column

1

Download demo query – 1.6 KB

Introduction

Recently I have answered one of the Code Project questions, in brief the question was, how to split the delimited string and return as row values. This is one of the common questions we all are facing in our development. Not only splitting the delimited string, but also we struggle when creating the delimited strings using SQL.

In this article, I will show you how easily this can be done by using the SQL XQuery.

Creating delimited strings

Suppose we have a table called DeveloperProject to store the individual developer project list.

DECLARE @DeveloperProject Table(
 DeveoperID INT,
 ProjectCode VARCHAR(20)
)
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'REPT_MOD')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(3,'REPT_MOD')

Now we need to convert this as follows

DeveoperID  ProjectCodes
----------- ------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD

There are many ways to do this. But here is the easiest way.

SELECT DeveoperID,
    REPLACE((SELECT ProjectCode AS'data()'
        FROM @DeveloperProject dp2
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')),' ',',')AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID
Instead of REPLACE function you can also use the SUBSTRING function as below
SELECT DeveoperID,
    SUBSTRING((SELECT ','+ ProjectCode
        FROM @DeveloperProject dp2
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')), 2, 8000)AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID
Table with delimited column

FOR XML() clause

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.
http://msdn.microsoft.com/en-us/library/ms178107.aspx

data() function

If you want to get the value of an attribute, rather than the attribute node itself, we can use the data() method.

For ex: if you execute the following query, you will get the xml output with <ProjectCode> attribute nodes.

SELECT ProjectCode
FROM @DeveloperProject c2
FOR XML PATH('')

XML PATH with Nodes
Now run it as follows, and see the result set. It gives the output without <ProjectCode> nodes.

SELECT ProjectCode AS 'data()'
FROM @DeveloperProject c2
FOR XML PATH('')

XML PATH without Nodes

Splitting delimited string

Suppose we have a table called DeveloperProjectCSV to store the developer id and comma separated project list

DECLARE @DeveloperProjectCSV Table(
    DeveoperID INT,
    ProjectCodes  VARCHAR(MAX)
)

We can use previously created script to insert the data to this table.

INSERT INTO @DeveloperProjectCSV(DeveoperID,ProjectCodes)
SELECT DeveoperID,
    REPLACE((SELECT
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

Now the table contains following data

DeveoperID  ProjectCodes
----------- ------------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD

And we need to split the project codes for each developer.

;WITH cte AS (
    SELECT
        DeveoperID,
        CAST('' + REPLACE(ProjectCodes, ',', '') + '' AS XML) AS ProjectCodes
    FROM @DeveloperProjectCSV
)
SELECT
    DeveoperID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS ProjectCode
FROM cte
CROSS APPLY ProjectCodes.nodes('//r') AS xTable(xColumn)

Splited string

WITH (common_table_expression)

WITH is used to specify the temporary named result set. In above WITH block creates temporary results set with two columns. One is a DeveloperID and other one is an xml type column for ProjectCodes
http://msdn.microsoft.com/en-us/library/ms175972.aspx

nodes() Method

The nodes() method is useful when you want to share an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

nodes (XQuery) as Table(Column)

http://msdn.microsoft.com/en-us/library/ms188282(v=sql.100).aspx

CROSS APPLY

CROSS APPLY is just like an inner join query clause which allows joining between two table expressions.
http://technet.microsoft.com/en-us/library/ms175156.aspx


Creating delimited strings with SQL 2000

Since SQL 2000 doesn’t support XML PATH, above query doesn’t work with SQL 2000. As an alternative option we can use COALESCE or ISNULL function and create User Defined Function to get the concatenation output.
Suppose we have a table called “DeveloperProject” with following data inserted.

DeveloperID ProjectCode
----------- --------------------
1           CODE_PROJECT
1           MSFT
1           REPT_MOD
2           MSFT
2           CODE_PROJECT
3           REPT_MOD

First we have to create the concatenation function

CREATE FUNCTION dbo.rowToCSV(@DeveoperID int )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @ProjectCodes VARCHAR(MAX)
    SELECT @ProjectCodes = COALESCE(@ProjectCodes + ', ', '') + ProjectCode
    FROM DeveloperProject
    WHERE DeveoperID = @DeveoperID
    RETURN @ProjectCodes
END

By using the above function, you will can get the same output.

SELECT
DeveoperID,
ProjectCodes = dbo.rowToCSV(DeveoperID )
FROM DeveloperProject
GROUP BY DeveoperID

Delimited String

COALESCE and ISNULL

COALESCE returns the first nonnull expression among its arguments. This is just like ISNULL function in SQL. Both ISNULL and COALESCE can be used to get the same results but there are some differences. http://msdn.microsoft.com/en-us/library/ms190349(v=sql.90).aspx

Splitting delimited string with SQL 2000
Again, SQL 2000 doesn’t support WITH (Common table expression) and the APPLY operators. As an alternative option we can use the spt_values master table.

SELECT  a.DeveloperID,
SUBSTRING(',' + a.ProjectCodes + ',', n.Number + 1, CHARINDEX(',', ',' + a.ProjectCodes + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM DeveloperProjects AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.ProjectCodes + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
    AND n.Number > 0
    AND n.Number < LEN(',' + a.ProjectCodes + ',')

spt_values

spt_values is an undocumented system table, which contains various data items used by Microsoft written stored procedures. We can use the rows with type = ‘P’ to have a numbers table with the numbers from 1-2047.

Mobile Emulators and Simulators

0

The most useful tools for mobile web are emulators and simulators. Following link provides the download resources for hundreds of emulators and simulators.

Sample Screen

Click here to download the list of emulators and simulators

for more info: http://www.mobilexweb.com/emulators

Adding styles dynamically to ASP.NET page header

2

Recently when I was working on the ASP.NET web site I wanted to modify the page styles dynamically. I googled and found that there are various technique out there for this. In this Article, I wish to share those techniques with.

Following are some of the techniques out there to accomplish this.

1. By using Page.header.Controls.Add()

Page.Header property gets the document header of the page if the head element is defend with “runat=server”  tag in the page declaration.The Header property gets a reference to an HtmlHead object that you can use to set document header information for the page. The HtmlHeadallows you to add information such as style sheets, style rules, a title, and metadata to the head element.

http://msdn.microsoft.com/en-us/library/system.web.ui.page.header.aspx

By using a literal control you can include the CSS file or even a style set.

Literal cssFile = new Literal()
{
    Text = @"<link href=""" + Page.ResolveUrl(styleFilePath) + @""" type=""text/css"" rel=""stylesheet""/>"
};
Page.Header.Controls.Add(cssFile);
Page.Header.Controls.Add(
new LiteralControl(
@"<style type='text/css'>
        .myCssClass
        {
background: black;
border: 1px solid;
        }
</style>
    "
));

Or HtmlGenericControl can use to add the styles file

//// Insert css file to header
HtmlGenericControl oCSS = oCSS = newHtmlGenericControl();
oCSS.TagName = "link";
oCSS.Attributes.Add("href", "css/mystyle.css");
oCSS.Attributes.Add("rel", "stylesheet");
oCSS.Attributes.Add("type", "text/css");
this.Page.Header.Controls.Add(oCSS);

2.  Page.Header.Stylesheet

More elegant technique is use of the Page.Header.Stylesheet Instead of using the string to defend styles (like above with Literal) you can use the style properties to create this.

//// Create dynamic style rule which applies to the css class selector (“.MyCssClass”)
Style dynamicClassStyle = new Style();
dynamicClassStyle.BorderStyle = BorderStyle.Solid;
dynamicClassStyle.BorderColor = System.Drawing.Color.Black;
dynamicClassStyle.BorderWidth = new Unit(1);
dynamicClassStyle.BackColor = System.Drawing.Color.White;
Page.Header.StyleSheet.CreateStyleRule(dynamicClassStyle, null, ".MyCssClass");
//// Create dynamic style rule which applies to type selector ("DIV")
Style dynamicTypeStyle = new Style();
dynamicTypeStyle.BorderStyle = BorderStyle.Solid;
dynamicTypeStyle.BorderColor = System.Drawing.Color.Black;
dynamicTypeStyle.BorderWidth = new Unit(1);
dynamicTypeStyle.BackColor = System.Drawing.Color.White;
Page.Header.StyleSheet.CreateStyleRule(dynamicTypeStyle, null, "DIV");

For more information please refer
http://msdn.microsoft.com/en-us/library/system.web.ui.istylesheet.createstylerule.aspx

With whatever the technique you follow please remember the following hints also,

Adding styles programmatically using the methods of the IStyleSheet interface during asynchronous postbacks is not supported. When you add AJAX capabilities to a Web page, asynchronous postbacks update regions of the page without updating the whole page.

Web service and Script service

0

Most of you are familiar about writing a web service using c#. Visual Studio provides the project template which easily creates a web service for you.

Visual Studio Project Template

Hello world web service

I’m sure you may know how to call this web service using c#. But do you know how to call the web service using client side scripts such as JavaScript? There are various ways to do this. I will demonstrate this using jQuery$.ajax() function.

        function callSvc() {
            $.ajax({
                type: "POST",
                url: "Service1.asmx/HelloWorld",
                data: "",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(data, status) { alert(data); },
                error: function(request, status, error) { alert(request); alert(status); alert(error); }
            });
        }
    

When you call the HelloWorld() service, by default you will get “500 – Internal server error” error message.

JavaScript debug mode

This error raised because you haven’t configured this service to access using client side scripts. When you Check the responseText, you will notice that it says “Only Web services with a [ScriptService] attribute on the class definition can be called from script.

To resolve this you have to configure your web service with [ScriptService] attribute.

[System.Web.Script.Services.ScriptService]
public class Service1 : System.Web.Services.WebService

When adding [ScriptService] attribute to the web service, it gets automatically handled by the ScriptHandlerFactory and creates a JavaScript proxy class for the web service. That means, you will be able to call the web service from the client side script same way as you call it from the code behind. You can view the generated proxy class by using the following command.

http://<URL>/SimpleWebService.asmx/js
http://<URL>/SimpleWebService.asmx/jsdebug

Compare the generated proxy class with the service description (http://<URL>/Service1.asmx?WSDL) and let me know what you noticed.

Download Demo Project