Wednesday, August 13, 2008

SQL Query to fetch in csv format

Query to fetch in csv format

DECLARE @theList varchar(100)
SELECT
@theList = COALESCE(@theList + ', ', '') + RTRIM(ACCOMPLISHMENT)
FROM PS_ACCOMPLISHMENTS
WHERE EMPLID = '3820001'
SELECT @theList

Removing new line character from a string in SQR

Removing new line character in a string

let $enter = chr(13) chr(10)
let $newComment = replace($Comment, $enter, ' ')

Lpad in SQL Server

By converting to a string and padding with the string..

Eg:

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),'7'),6)

SELECT RIGHT(REPLICATE('0',6)+ CONVERT(VARCHAR(6),SUBSTRING(RTRIM('T000006'),2,LEN(MAX('T000006'))-1)+1),6) FROM PS_PERSON