SQL: Column Values as Comma Separated String

I have a table as shown below, which has Country_Code and languages that are spoken by different people living in a country. I need to retrieve distinct languages spoken in a country based on the Country_Code column as a comma separated string.

SQL Statement

The following statement shows how it can be achieved:

 Collapse | Copy Code

declare @str varchar(1000)

SELECT @str= coalesce(@str + ', ', '') + a.CountryLang_Desc 
FROM (SELECT DISTINCT CountryLang_Desc from CountryLanguages where Country_Code='IN') a

print @str 

Split Function in Sql Server 2008

create a Split Function using below code

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
WITH Split(stpos,endpos) 
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO

EX:- CompanyAddress =dsfsdf `Adhaura`Bihar`India

calling this function using below code

select data from dbo.Split(CompanyAddress,'`')where id = 2

o/p:- Adhaura

Dynamic Date Validation using JavaScript

Here explained how one can validate date without using any Custom Validator or Regular Expression using JavaScript

Here I am explaining Validation of date using JavaScript. Along with validation it will also enforce Date Format restrictions dynamically as the user types the date in the textbox.

The whole idea of the tutorial is to dynamically validate the data and display the results when the user is typing the same. The advantage of this approach is that it allows the user to correct its mistake first and the move to the next item

The article will cover the following aspects of data validation

1.     Enforcing date format dynamically i.e dd/mm/yyyy

2.     Validating date dynamically on key events.

Validation and Date format Enforcement

The DateFormat function enforces the following conditions

1.     The characters entered should be numeric

2.     Automatically appends slashes as the user types it

It enforces numeric character check based on the keyboard key values thus simply do not allow the user to enter any character except number.

For more information on this validation refer the article TextBox Validation using JavaScript

The next function ValidateDate as the name suggest simply validates the date. Using the JavaScript date functions and notifies the user whether the data is valid or not.

The compete script code for the two functions is given below

var isShift = false;

                var seperator = "-";

                function DateFormat(txt, keyCode) {

                    if (keyCode == 16)

                        isShift = true;

                    //Validate that its Numeric

                    if (((keyCode >= 48 && keyCode <= 57) || keyCode == 8 || keyCode <= 37 || keyCode <= 39 || (keyCode >= 96 && keyCode <= 105)) && isShift == false) {

                        if ((txt.value.length == 2 || txt.value.length == 5) && keyCode != 8) {

                            txt.value += seperator;

                        }

                        return true;

                    }

                    else {

                        return false;

                    }

                }

                function ValidateDate(txt, keyCode) {

                    if (keyCode == 16)

                        isShift = false;

                    var val = txt.value;

                    if (val.length == 10) {

                        var splits = val.split("-");

                        var dt = new Date(splits[1] + "/" + splits[0] + "/" + splits[2]);

                        //Validation for Dates

                        if (dt.getDate() == splits[0] && dt.getMonth() + 1 == splits[1] && dt.getFullYear() == splits[2]) {

                            //  alert("Valid Date");

                        }

                        else {

                            alert("InValid Date");

                            txt.value = '';

                            return;

                        }

                    }

                    else if (val.length < 10) {

                    }

                }

<asp:TextBox ID="txtLetterDate" CssClass="TextBox" runat="server" MaxLength="10"

                                                    onkeyup = "ValidateDate(this, event.keyCode)" onkeydown = "return DateFormat(this, event.keyCode)" Height="24px"></asp:TextBox>

Exposing printer settings to the SYSTEM account

Server-side printing

When you call the Print method, the DataWindow is printed to a printer that is installed on the Web server. The DataWindow can reside in either a WebDataWindowControl or a DataStore.

The Web server must be configured so that the ASP.NET worker process has access to system settings and the SYSTEM account has access to printers. Implementing server-side printing requires changing the default permissions on the server.

Configuring the .NET Framework

By default, the .NET Framework runs with the permissions of the local “machine” account. In order to print using IIS, the .NET Framework must run with the permissions of the local “SYSTEM” account. The procedures for configuring the .NET Framework for IIS 5.x and 6.x are different.

On Windows 2000 or Windows XP with IIS 5.x, you need to edit the machine.config file on the Web server to ensure that the process under which ASP.NET is running has sufficient permissions to access printers installed on the network.

On Windows Server 2003 and on Windows 2000 or Windows XP with IIS 6.x, you use the IIS Manager (the inetmgr utility) to configure the application pool identity.

To configure the .NET Framework to run with local SYSTEM settings with IIS 5.x:

1. In your C:\WINDOWS directory, navigate to the Microsoft.NET\Framework\VersionNum\CONFIG directory, where VersionNum is the version of the .NET Framework, for example v2.0.50727.
2. Open the machine.config file with a text or XML editor and locate the processModel element.
   You need to add or change the value of the userName setting in this element. The default settings for userName and password are:

   userName="machine" password="AutoGenerate"
   

3. Change the value of userName to “SYSTEM”:

   userName="SYSTEM" password="AutoGenerate"
   

4. Save the machine.config file.

To configure the .NET Framework to run with local SYSTEM settings with IIS 6.x:

1. In the Windows Start>Run box, type InetMgr.
2. In the IIS Manager, expand local computer and select Application Pools.
3. Right-click Application Pools and select Properties.
4. Click the Identity tab, select Local System from the Predefined list box, and click OK.
5. Click Yes in the pop-up warning message window that displays.
6. Right-click local computer and select All Tasks>Restart IIS to restart IIS.

Exposing printer settings to the SYSTEM account

When a printer is installed on a computer, its settings are stored in the HKEY_CURRENT_USER registry key. IIS runs under the context of the local SYSTEM account. It has access to the HKEY_USERS registry key, but not to the HKEY_CURRENT_USERS subkey, which is only available to a user logged on to the computer.

By default, no printers are defined in the HKEY_USERS key. You can add them by exporting three keys from the HKEY_CURRENT_USERS key and importing them to the HKEY_USERS key.

Caution Incorrectly editing the registry might severely damage your system. Make sure you back up valued data before making changes to the registry.

To make printer settings available to the SYSTEM account:

1. Check that the current user on the Web server has the required printer(s) installed.
2. To launch the Registry Editor, type regedit in the Start>Run dialog box and click OK.
3. Select the HKEY_USERS\.DEFAULT\Software\Microsoft\Windows NT\CurrentVersion key and export the registry key from the File or Registry menu.
4. Specify a name and location in the Export Registry File dialog box and click Save.
   This file provides a backup if you need to restore the registry.
5. In the HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion key, select the Devices subkey and export the registry key.
6. Specify the name devices.reg and a temporary location in the Export Registry File dialog box and click Save.
7. Repeat steps 5 and 6 for the PrinterPorts and Windows subkeys, naming the files printerports.reg and windows.reg.
8. Open devices.reg in Notepad (do not use TextPad or another editor), replace the string HKEY_CURRENT_USER with the string HKEY_USERS\.DEFAULT (note that there is a dot before DEFAULT), and save the file.
9. Repeat step 8 for printerports.reg and windows.reg.
10. Double-click each of the edited files to import them into the registry.
11. Restart IIS so that the configuration changes take effect.

To restart IIS:

1. In the Windows Start>Run box, type InetMgr.
2. In the IIS Manager, right-click the local computer and select All Tasks>Restart IIS.
3. In the Start/Stop/Reboot dialog box, select Restart Internet Services on ComputerName, where ComputerName is the local computer.

After restarting IIS, you need to restart the Web site.

Where the DataWindow is printed

When you use the Print method, the DataWindow is printed to the printer specified in the DataWindow object’s PrinterName property, or to the default printer as specified in the Devices registry key.

This Visual Basic code gets the name of the printer and writes it to a log file before printing the DataWindow:

Dim printerName As String
printerName = dwGrid.PrintProperties.PrinterName
sMsg = "Printer name: " + printerName
writelog(sMsg)
dwGrid.Print()

Searching and finding a string value in all columns in a SQL Server table

Problem

Sometimes there is a need to find if a string value exists in any column in your table.  Although there are system stored procedures that do a "for each database" or a "for each table", there is not a system stored procedure that does a "for each column".  So trying to find a value in any column in your database requires you to build the query to look through each column you want to search using an OR operator between each column.  Is there any way this can be dynamically generated?

Solution

Once again this is where T-SQL comes in handy along with the use of system tables or system views.  The code below allows you to search for a value in all text data type columns such as (char, nchar, ntext, nvarchar, text and varchar). 

The stored procedure gets created in the master database so you can use it in any of your databases and it takes three parameters:

• stringToFind - this is the string you are looking for.  This could be a simple value as 'test' or you can also use the % wildcard such as '%test%', '%test' or 'test%'.
• schema - this is the schema owner of the object
• table - this is the table name you want to search, the procedure will search all char, nchar, ntext, nvarchar, text and varchar columns in the table

The first thing you need to do is create this stored procedure by copying the below code and executing it in a query window.

USE master
GO

CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

DECLARE @sqlCommand VARCHAR(8000) DECLARE @where VARCHAR(8000) DECLARE @columnName sysname
DECLARE @cursor VARCHAR(8000)
BEGIN TRY
   SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE'
   SET @where = ''

   SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
   FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA = ''' + @schema + '''
   AND TABLE_NAME = ''' + @table + '''
   AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

   EXEC (@cursor)

   OPEN col_cursor   
   FETCH NEXT FROM col_cursor INTO @columnName   

   WHILE @@FETCH_STATUS = 0   
   BEGIN   
       IF @where <> ''
           SET @where = @where + ' OR'

       SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + ''''
       FETCH NEXT FROM col_cursor INTO @columnName   
   END   

   CLOSE col_cursor   
   DEALLOCATE col_cursor 

   SET @sqlCommand = @sqlCommand + @where
   --PRINT @sqlCommand
   EXEC (@sqlCommand)  END TRY
BEGIN CATCH
   PRINT 'There was an error. Check to make sure object exists.'
   IF CURSOR_STATUS('variable', 'col_cursor') <> -3
   BEGIN
       CLOSE col_cursor   
       DEALLOCATE col_cursor 
   END
END CATCH

Once the stored procedure has been created you can run some tests.

Here are some tests that were done against the AdventureWorks database.

Find the value 'Irv%' in the Person.Address table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable 'Irv%', 'Person', 'Address'

Find the value '%land%' in the Person.Address table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Address'

Find the value '%land%' in the Person.Contact table.

USE AdventureWorks
GO
EXEC sp_FindStringInTable '%land%', 'Person', 'Contact'

That's all there is to it.  Once this has been created you can use this against any table and any database on your server.

SQL Server Cursor Example

Problem

In my T-SQL code I always use set based operations.  I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing.  I know cursors exist but I am not sure how to use them.  Can you provide some cursor examples?  Can you give any guidance on when to use cursors?  I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.

Solution

In some circles cursors are never used, in others they are a last resort and in other groups they are used regularly.  In each of these camps they have different reasons for their stand on cursor usage.  Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.  So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not.  To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name   END  

CLOSE db_cursor  
DEALLOCATE db_cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE <cursor_name> CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

What is Normalization in SQL ?

Defination : Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. There are several benefits for using Normalization in Database.

Benefits :

1. Eliminate data redundancy
2. Improve performance
3. Query optimization
4. Faster update due to less number of columns in one table
5. Index improvement

There are diff. - diff. types of Normalizations form available in the Database. Lets see one by one.

1. First Normal Form (1NF)

 First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
• Remove repetative groups
• Create Primary Key

Name	State	Country	Phone1	Phone2	Phone3
John	101	1	488-511-3258	781-896-9897	425-983-9812
Bob	102	1	861-856-6987
Rob	201	2	587-963-8425	425-698-9684
PK			[ Phone Nos ]
?				?
ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684

2. Second Normal Form (2NF)Second normal form (2NF) further addresses the concept of removing duplicative data:

·         Meet all the requirements of the first normal form.

·         Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

·         Create relationships between these new tables and their predecessors through the use of foreign keys.   

Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship

ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684
ID	Name	State	Country		PhoneID	ID	Phone
1	John	101			1	1	488-511-3258
2	Bob	102			2	1	781-896-9897
3	Rob	201			3	1	425-983-9812
					4	2	587-963-8425
					5	3	587-963-8425
					6	3	425-698-9684

3. Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

·         Meet all the requirements of the second normal form.

·         Remove columns that are not dependent upon the primary key.

  Country can be derived from State also… so removing country

ID	Name	State	Country
1	John	101	1
2	Bob	102	1
3	Rob	201	2

4. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

·         Meet all the requirements of the third normal form.

·         A relation is in 4NF if it has no multi-valued dependencies.

If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it

The 4NF also known as BCNF NF

TeacherID	StudentID	SubjectID	StudentName
101	1001	1	John
101	1002	2	Rob
201	1002	3	Bob
201	1001	2	Rob
TeacherID	StudentID	SubjectID	StudentName
101	1001	1	X
101	1002	2	X
201	1001	3	X
201	1002	2	X