Developers Network
Create A Comma Delimited List From a Column
In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).
For example here is our Sample Table -
| Id | Names |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | A |
| 2 | B |
| 3 | X |
| 3 | Y |
| 3 | Z |
And here is the expected output -
| Id | AllNames |
| 1 | A, B, C |
| 2 | A, B |
| 3 | X, Y, Z |
Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.
Create Sample Data -
| --Create sample table CREATE TABLE Test ( Id INT, Names VARCHAR(100) ) GO -- Load sample data INSERT INTO Test SELECT 1,'A' UNION ALL SELECT 1,'B' UNION ALL SELECT 1,'C' UNION ALL SELECT 2,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'X' UNION ALL SELECT 3,'Y' UNION ALL SELECT 3,'Z' GO |
SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -
| SELECT T1.Id |
If the column “Names” doesn’t contain any spaces in between its values then here is another solution -
2nd solution -
| SELECT T1.Id |
Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.
Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.
E.g. for Vertical Bar
| SELECT T1.Id |
Work out min and hours
I have a minute value and i want to have to 2 string values one with how many hours and the other with the minutes, e.g.:
Value - 121 minutes
var span = System.TimeSpan.FromMinutes(121);
var hours = ((int)span.TotalHours).ToString();
var minutes = span.Minutes.ToString();
The ToString() is because you asked for string values ...
TotalHours are the complete hours in the TimeSpan, they can be more than 24 (whereas the "Hours" field has a maximum of 24)
Oh, and on second thought: Why use the TimeSpan and not calculate it yourself? Because TimeSpan is already there debugged & tested by Microsoft, it has a nice clean interface (looking at the code you easily see whats going on without having to follow a calculation mentally) and it easily extends to further solutions. (Have the input in seconds? Use TimeSpan.FromSeconds(). Want the days? Use span.TotalDays ...)
Update:
I just noticed mistake in my answer: TotalHours returns a fractional value of all the hours, so we have to truncate it to an integer before converting it to a string.
Value - 121 minutes
var span = System.TimeSpan.FromMinutes(121);
var hours = ((int)span.TotalHours).ToString();
var minutes = span.Minutes.ToString();
The ToString() is because you asked for string values ...
TotalHours are the complete hours in the TimeSpan, they can be more than 24 (whereas the "Hours" field has a maximum of 24)
Oh, and on second thought: Why use the TimeSpan and not calculate it yourself? Because TimeSpan is already there debugged & tested by Microsoft, it has a nice clean interface (looking at the code you easily see whats going on without having to follow a calculation mentally) and it easily extends to further solutions. (Have the input in seconds? Use TimeSpan.FromSeconds(). Want the days? Use span.TotalDays ...)
Update:
I just noticed mistake in my answer: TotalHours returns a fractional value of all the hours, so we have to truncate it to an integer before converting it to a string.
Extended Date Formats
| Extended Date Formats | ||
| Date Format | SQL Statement | Sample Output |
| YY-MM-DD |
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
|
99-01-24 |
| YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
|
1999-01-24 |
| MM/YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] |
08/99 |
| MM/YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] | 12/2005 |
| YY/MM | SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] | 99/08 |
| YYYY/MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] | 2005/12 |
| Month DD, YYYY 1 | SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] | July 04, 2006 1 |
| Mon YYYY 1 | SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] | Apr 2006 1 |
| Month YYYY 1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] | February 2006 1 |
| DD Month 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] | 11 September 1 |
| Month DD 1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] | September 11 1 |
| DD Month YY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] | 19 February 72 1 |
| DD Month YYYY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] | 11 September 2002 1 |
| MM-YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] |
12/92 |
| MM-YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] | 05-2006 |
| YY-MM | SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] |
92/12 |
| YYYY-MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] | 2006-05 |
| MMDDYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] | 122506 |
| MMDDYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] | 12252006 |
| DDMMYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] | 240702 |
| DDMMYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] | 24072002 |
| Mon-YY 1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] | Sep-02 1 |
| Mon-YYYY 1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] | Sep-2002 1 |
| DD-Mon-YY 1 | SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] | 25-Dec-05 1 |
| DD-Mon-YYYY 1 | SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] | 25-Dec-2005 1 |
Standard Date Formats
| Standard Date Formats | |||
| Date Format | Standard | SQL Statement | Sample Output |
| Mon DD YYYY 1 HH:MIAM (or PM) |
Default | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Jan 1 2005 1:29PM 1 |
| MM/DD/YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] | 11/23/98 |
| MM/DD/YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] | 11/23/1998 |
| YY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] | 72.01.01 |
| YYYY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] | 1972.01.01 |
| DD/MM/YY | British/French | SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] | 19/02/72 |
| DD/MM/YYYY | British/French | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | 19/02/1972 |
| DD.MM.YY | German | SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] | 25.12.05 |
| DD.MM.YYYY | German | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] | 25.12.2005 |
| DD-MM-YY | Italian | SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] | 24-01-98 |
| DD-MM-YYYY | Italian | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | 24-01-1998 |
| DD Mon YY 1 | - | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] | 04 Jul 06 1 |
| DD Mon YYYY 1 | - | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | 04 Jul 2006 1 |
| Mon DD, YY 1 | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] | Jan 24, 98 1 |
| Mon DD, YYYY 1 | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Jan 24, 1998 1 |
| HH:MM:SS | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | 03:24:53 |
| Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 | Default +
milliseconds |
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | Apr 28 2006 12:32:29:253PM 1 |
| MM-DD-YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] | 01-01-06 |
| MM-DD-YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | 01-01-2006 |
| YY/MM/DD | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] | 98/11/23 |
| YYYY/MM/DD | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] | 1998/11/23 |
| YYMMDD | ISO | SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] | 980124 |
| YYYYMMDD | ISO | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] | 19980124 |
| DD Mon YYYY HH:MM:SS:MMM(24h) 1 | Europe default + milliseconds | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 28 Apr 2006 00:34:55:190 1 |
| HH:MI:SS:MMM(24H) | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] | 11:34:23:013 |
| YYYY-MM-DD HH:MI:SS(24h) | ODBC Canonical | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | 1972-01-01 13:42:24 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) | ODBC Canonical (with milliseconds) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 1972-02-19 06:35:24.489 |
| YYYY-MM-DDTHH:MM:SS:MMM | ISO8601 | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | 1998-11-23T11:25:43:250 |
| DD Mon YYYY HH:MI:SS:MMMAM 1 | Kuwaiti | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | 28 Apr 2006 12:39:32:429AM 1 |
| DD/MM/YYYY HH:MI:SS:MMMAM | Kuwaiti | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | 28/04/2006 12:39:32:429AM |
Proper Title Case
CREATE FUNCTION fn_ProperCase(@x varchar(7999))
RETURNS varchar(7999)
AS
BEGIN
DECLARE @y int
SET @y = 1
SELECT @x = UPPER(SUBSTRING(@x,1,1))+LOWER(SUBSTRING(@x,2,LEN(@x)-1))+' '
WHILE @y < LEN(@x)
BEGIN
SELECT @y=CHARINDEX(' ',@x,@y)
SELECT @x=SUBSTRING(@x,1,@y)+UPPER(SUBSTRING(@x,@y+1,1))+SUBSTRING(@x,@y+2,LEN(@x)-@y+1)
SELECT @y=@y+1
END
RETURN @x
END
SELECT dbo.fn_ProperCase('THE QUICK FOX JUMPED OVER THE LAZY DOG')
RETURNS varchar(7999)
AS
BEGIN
DECLARE @y int
SET @y = 1
SELECT @x = UPPER(SUBSTRING(@x,1,1))+LOWER(SUBSTRING(@x,2,LEN(@x)-1))+' '
WHILE @y < LEN(@x)
BEGIN
SELECT @y=CHARINDEX(' ',@x,@y)
SELECT @x=SUBSTRING(@x,1,@y)+UPPER(SUBSTRING(@x,@y+1,1))+SUBSTRING(@x,@y+2,LEN(@x)-@y+1)
SELECT @y=@y+1
END
RETURN @x
END
SELECT dbo.fn_ProperCase('THE QUICK FOX JUMPED OVER THE LAZY DOG')
SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Functin - How to Get Leap Year
/*Detecting leap year*/
CREATE FUNCTION dbo.IsLeapYear(@Year int)
RETURNS bit
AS
BEGIN
DECLARE @RetVal bit
IF (@Year % 400) = 0 --//every 400 years
SET @RetVal = 1
ELSE
BEGIN
IF (@Year % 100) = 0
SET @RetVal = 0
ELSE
BEGIN
IF (@Year % 4) = 0
SET @RetVal = 1
ELSE
SET @RetVal = 0
END
END
RETURN @RetVal
END
GO
CREATE FUNCTION dbo.IsLeapYear(@Year int)
RETURNS bit
AS
BEGIN
DECLARE @RetVal bit
IF (@Year % 400) = 0 --//every 400 years
SET @RetVal = 1
ELSE
BEGIN
IF (@Year % 100) = 0
SET @RetVal = 0
ELSE
BEGIN
IF (@Year % 4) = 0
SET @RetVal = 1
ELSE
SET @RetVal = 0
END
END
RETURN @RetVal
END
GO
MaxLength not working in ASP.NET textbox multiLine
Great code I found for maxlength issue with .NET textboxes. .NET textboxes, if TextMode is set to MutilLine, the maxlength property just doesn't work, sux. So after playing around with different soloutions. I found this on, thanks to a guy named "Leo." So a big thanks to him!
Add this function in JavaScript on your page, I stuck it my "functions.js" file I include on the page anyway.
function checkMaxLen(txt,maxLen) {
try{
if(txt.value.length > (maxLen-1)) {
var cont = txt.value;
txt.value = cont.substring(0,(maxLen -1));
return false;
};
}catch(e){
}
}
Then on the textbox use something like this:
Notice the function passes the textbox (this) and the maxlength you want, in this case 150, notice I have 151, the function will limit the maxlength minus one... So if you want to limit the textbox with mutiline set to 100, you enter 101, make sense?
Add this function in JavaScript on your page, I stuck it my "functions.js" file I include on the page anyway.
function checkMaxLen(txt,maxLen) {
try{
if(txt.value.length > (maxLen-1)) {
var cont = txt.value;
txt.value = cont.substring(0,(maxLen -1));
return false;
};
}catch(e){
}
}
Then on the textbox use something like this:
<asp:TextBox runat="server" ID="txtComments" CssClass="comment_textbox" Height="75px" TextMode="MultiLine" onkeyup="return checkMaxLen(this,151)"></asp:TextBox>Notice the function passes the textbox (this) and the maxlength you want, in this case 150, notice I have 151, the function will limit the maxlength minus one... So if you want to limit the textbox with mutiline set to 100, you enter 101, make sense?
Linked Server - How to pass a variable to a linked server query
This article describes how to pass a variable to a linked server query.
When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query.
To pass a variable to one of the pass-through functions, you must build a dynamic query.
Any data that includes quotes needs particular handling. For more information, see the "Using char and varchar Data" topic in SQL Server Books Online and see the following article in the Microsoft Knowledge Base:
When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query.
To pass a variable to one of the pass-through functions, you must build a dynamic query.
Any data that includes quotes needs particular handling. For more information, see the "Using char and varchar Data" topic in SQL Server Books Online and see the following article in the Microsoft Knowledge Base:
Pass Basic Values
When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample: DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT @VAR = 'CA'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)
Pass the Whole Query
When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), use code that is similar to the following sample:DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL)
Use the Sp_executesql Stored Procedure
To avoid the multi-layered quotes, use code that is similar to the following sample:DECLARE @VAR char(2)
SELECT @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR
Subscribe to:
Posts (Atom)