SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2

In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.

I have re-written the same function with function STUFF, and it removes any limit imposed on the script.

USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO

Do let me know your thoughts on the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Featured on Channel 9

This blog was featured on Channel 9 MSDN over here : TWC9: Scott Hanselman, Jon Galloway, Bing, parallel unit tests, more. I was very proud that this blog was discussed for more than 5 mins (from min 18 to min 23) on my favorite online show. Scott Hanselman, Jon Galloway along with Dan Fernandez make this show very live and very very entertaining.

The article which was featured in the show is SQL SERVER – Comma Separated Values (CSV) from Table Column.

Here are few screenshot from the show.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Remove Duplicate Entry from Comma Delimited String – UDF

I love reader’s contribution this blog as that brings variety in articles. I encourage my readers to provide their contribution and I will publish then with their name.

Blog Reader Ashish Jain has posted very simple script which will remove duplicate entry from comma delimited string. User Defined Function has very simple logic behind it. It takes comma delimited string and then converts it to table and runs DISTINCT operation on the table. DISTINCT operation removes duplicate value. After that it converts the table again into the string and it can be used.

I have modified original contribution from Ashish so now it completely covers the subject intended to cover. I would suggest that this UDF should be kept handy to perform this tedious task easily.

CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR
(MAX)
AS
BEGIN
DECLARE
@ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET
@list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT
@rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
GO

I encourage my readers to send their contribution as well so I can include their contribution as well.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only

SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE
@intAlpha INT
SET
@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE
@intAlpha > 0
BEGIN
SET
@strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN
ISNULL(@strAlphaNumeric,0)
END
GO

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';
GO

As result of above script self explainary, I will be not describing it in detail. If you use any alternative method to do the same task, please let me know.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

After reading my article SQL SERVER – 2008 – TRIM() Function – User Defined Function, I have received email and comments where user are asking if it is possible to remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.

I found following script posted by Russ and Erik. It is modified a bit from original script.

CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), 8000)
RETURN @str
END
GO
CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END
GO
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX('         word leading trailing spaces           ') AS 'TrimmedWord'
GO

Running above function will give you result where there is no white spaces or any other while space like chars.

Here is the quick video on the same subject:

Let me know what you think about this and if this can be improved further.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Remove Duplicate Characters From a String

Follow up of my previous article of Remove Duplicate Chars From String here is another great article written by Madhivanan where similar solution is suggested with alternate method of Number table approach. Check out Remove duplicate characters from a string

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)

Just a day ago I received phone call from my friend in Bangalore. He asked me

What do I think of storing images in database and what kind of datatype he should use?

I have very strong opinion about this issue.

I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.

Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)