TSQL Script to remove TAB, LINE FEED and CARRIAGE RETURN

The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Characters in a variable @String

[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
[/sql]

Extended script which also provides length of the string:
[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String
PRINT ‘LENGTH=’+CAST(LEN(@String) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))
[/sql]

Concatenating Data From Different Rows into Single Column Row

Consider a scenario where a table has multiple rows, and each of these rows belong to a group and the data from all the rows should be concatenated based on the group. This post explains different ways to accomplish this.

Sample data creation:
[sql]

CREATE TABLE GroupMembers(GroupName VARCHAR(100),MemberName VARCHAR(100))
GO

INSERT INTO GroupMembers
SELECT ‘Group1′,’A’
union
SELECT ‘Group1′,’B’
union
SELECT ‘Group1′,’C’
union
SELECT ‘Group1′,’D’
union
SELECT ‘Group1′,’E’
union
SELECT ‘Group1′,’F’
union
SELECT ‘Group1′,’G’
union
SELECT ‘Group1′,’H’
union
SELECT ‘Group2′,’A1’
union
SELECT ‘Group2′,’B1’
union
SELECT ‘Group2′,’C1’
union
SELECT ‘Group2′,’D1’
union
SELECT ‘Group2′,’E1’
union
SELECT ‘Group2′,’F1’
union
SELECT ‘Group2′,’G1’
union
SELECT ‘Group2′,’H1’
GO
SELECT GroupName,MemberName FROM GroupMembers
GO
[/sql]

The above created table has data that belongs to two groups Group1 and Group2.

Below TSQL Queries concatenate this data into single column/row for each group.
[sql]
–Sol1:
–Using XML
SELECT GroupName,
(SELECT MemberName
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName
GO

–Sol2:
–With out seperation between member names
–Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName

–Sol3:
–With comma seperation between member names
–Using XML, Without XML Attributes
SELECT GroupName,
(SELECT MemberName+ ‘,’ AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”))
FROM GroupMembers A
GROUP BY GroupName

–Sol4:
–With comma seperation between member names(no ending comma)
–Using XML, Without XML Attributes
SELECT G.GroupName,Left(G.Members,Len(G.Members)-1)
FROM (
SELECT GroupName,
(SELECT MemberName+ ‘,’ AS [text()]
FROM GroupMembers WHERE GroupName=A.GroupName
FOR XML PATH(”)) AS Members
FROM GroupMembers A
GROUP BY GroupName
) G
[/sql]